Count text cells in one column against criteria from another

[Mike]

Can anybody help with the following,


I have table numbers (1 - 10) in Col A and a menu choice (Text) in Col
C. I need to calculate the total of the menu choice for each table. I
have used =COUNTIF(C5:C100,"Prawn Cocktail")which gives me a total for
all ten tables, but I needing to calculate a total for each table.

Thanks in anticipation.

Mike

 

[Socko]

I know that there is a way to lookup with two conditions, but i can
tell you, the easiest way to do the same would be pivot table. You can
update the data on one worksheet and create pivot on the other. When
you want the updated data by menu item for each table, refresh the
pivot table and you will see teh updated data.

You may download the example worksheet...
http://www.sockofiles.350.com/menubytable.xls


Selva V Pasupathy
Visit: http://socko.wordpress.com

 

[Moily]

Hiya,

A pivot table works well but I find it very clumsy and prefer using the
sumproduct formula. Especially since the pivot table can't as easily be
adjusted for new rows (sumproduct can by find and replacing all a$10 with
a$20 if you've added 10 new rows) and the pivot table also has to be manually
refreshed whereas the sumproduct automatically updates.

You'll have to create a header row (say table numbers) and a header column
(say menu options) and input sumproduct formulas similar to the below.
=SUMPRODUCT((A$1:A$10=1)*($C$1:$C$10="Prawn Cocktail"))
=SUMPRODUCT((A$1:A$10=2)*($C$1:$C$10="Prawn Cocktail"))
=SUMPRODUCT((A$1:A$10=3)*($C$1:$C$10="Prawn Cocktail"))
etc

I find it even more intuitive to substitute the table number and menu item
in the formula with the header cell references:
assume that the matrix you're creating is in A14:K15 as there's one header
row (row 14) and only one menu item (row 15) but 10 tables (columns B through
K):
cell b15 would be: =SUMPRODUCT((A$1:A$10=b$14)*($C$1:$C$10=$a15))
then drag the formula to fit the entire table. It'll match your headings to
the data and find your answers.

Hope this helps - it's definitely saved me hours of work!!!
Cheers,
Ann

 

[Moily]

Sorry - meant to add that the biggest difference with the Pivot Chart is that
you can add many more variables. For instance, say that you had another
column (D) saying how many women vs. how many men chose the Prawn Cocktail.

=SUMPRODUCT((A$1:A$10=1)*($C$1:$C$10="Prawn Cocktail")*($C$1:$C$10="Female"))

or you wanted to find out age groups (say Column E) choosing this particular
item as well as age and table then:

=SUMPRODUCT((A$1:A$10=1)*($C$1:$C$10="Prawn
Cocktail")*($C$1:$C$10="Female")*($D$1:$D$10="45-60"))