COUNTIF with 3 criteria
Posted by Kirsten on July 11, 2001 11:54 AM
I am trying to do a create a formula that will give me a count of 3 pieces of criteria in a count(if) statement.
Example: In one column I have apples, oranges, and pears, out of the 7 apples in column one I need to know from column two, which contains Shaws, Star Market, Stop n Shop, how many apples went to shaws, and in the third column, which contains red, yellow, green how many were red apples that went to Shaws.
Hope you can help
Posted by IML on July 11, 2001 12:08 PM
Try the following:
=SUM((A1:A10="apples")*(B1:B10="shaw")*(C1:C10="red"))
This is an array formula so be sure to hit enter when control and shift are depressed. If done correctly, braces will appear around the formula in the bar.
good luck
Posted by Eric on July 11, 2001 12:47 PM
Pivot Table works too
Lable your columns Fruit, Store, and Color, select the column titles and all of the data, go to Data-->Pivot Table and Pivot Chart Report, leave step 1 and 2 of the wizard at their default values, go to step 3 of the wizard and click the "layout" button. Drag "store" and "color" buttons listed on the right hand side of dialogue box into the "ROW" section of the "PAGE", drag the "fruit" selection into the "DATA" section, click "OK" and see if that gives you what you want.
Hope that helps
