yogi_Count Instances Of Strings In A Column And Compute Net Difference By Item

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #659   Jul 27, 2012     www.energyefficientbuild.com.


user Jabus said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/9naciXcSJcY)
Spreadsheet - How to Compare Two String Columns and Add New Rows From Forms?
Hi there,

I'm creating a bit of an awkward spreadsheet, it's going to end up having 2 columns, 1 with the name of the product and the second with the words add, remove or disregard. An example of the spreadsheet:

  A            B

Printer   ||  Add 1
Printer   ||  Add 1

Printer   ||  Remove 1

Computer  ||  Add 1

Computer  ||  Add 1

Computer  ||  Add 1

Computer  ||  Remove 1

I want to get a formula that will compare grab the name of the product in column A and then count how many "Add 1"'s there are and subtract how many "Remove 1"'s. So the end result should show a total tally of Printers = 1 and Computers = 2

Note the following may hurt to read: I feel like I'm going about this a really long way around but I don't recall seeing a function, or perhaps my brain just is over complicating things. Because what I ended up doing is creating 4 new columns the first being an IF statement that generates a 1 if column B says "Add 1" and a 5 (random number) if false. Then the same thing for the second column (colmn D now) where I do an IF statement to see if the item is a Printer or not giving 1 or 0 as the true or false. Then my third column does an IF to check it column C is a 1 and column D is a 1 then that amount of add printers is 1 (which i then sum) then same thing for removing printers but I use the the 5 in the column C to add to the 1 from column D (confirming its a printer) and so that if 5+1 = 6 then it's a 1 for the remove column and then sum that up then the the sum of column e is subtracted from d to get the right answers.

But that's a horrible solution, anyone have an idea on how I can compare the two columns? and then have new formulas form as people respond to a form? I feel like I'm doing such a poor job of this.

--------------------------------------------------------------------------------------------

let us have a look at the following solution to the problem