Count if with multiple values

Microsoft Office 2000 standard
June 4, 2010 at 13:41:22
Specs: Microsoft Excel 2000

I have a spreadsheet full of data, 2 of the columns being "New" or "Old" and the other being products "OTN" and "OT7". I want to count the number of cells that are both "New" and "OTN" and "Old" and "OTN" (and the same for OT7.

I have tried using the countif and sumif formulas and can't find any that help. Any suggestions?

Thanks in advance!

See More: Count if with multiple values

Report •

June 4, 2010 at 14:59:37

you need an array formula

supposing you have "Yes" written in the column that signifies affirmative. So a Yes in column New mean it was New and a Yes in OTN meant it was an OTN and suppose New was in column A Old in column B OTN in column C and OT7 in D and there are 123 rows of data.

The number of New and OTN would be


you enter this, and then in edit mode hold down shift and Ctrl and hit Enter and it would give you the number of New and OTN


Report •

June 5, 2010 at 05:45:06

Not sure exactly how your data is arranged.
If you have two columns, one containing 'New' or 'Old' (column A) and the other containing 'OTN' or 'OT7' (column B) then this formula will work for data in rows 2 to 100:
In E2 put 'Old' and in F2 put 'OTN'
In E3 put 'Old' and in F3 put 'OT7'
In E4 put 'New' and in F4 put 'OTN'
In E5 put 'New' and in F5 put 'OT7'

In G2 enter: =SUMPRODUCT(($A$2:$A$100=E2)*($B$2:$B$100=F2))
Drag the formula down to extend it to G5

You will now have the totals for the 4 'conditions'.

SUMPRODUCT works like an array formula, but does not have to be entered with the Ctrl+Shift+Enter combination.
Note that SUMPRODUCT can be extended to test for multiple items - a third column with 'In stock' or 'On order' for example.

If your data is in 4 columns:
Col. A New or blank
Col. B Old or blank
Col. C OTN or blank
Col. D OT7 or blank
Then use these 4 formulas to get the numbers:
Old & OTN =SUMPRODUCT(($B$2:$B$100="Old")*($C$2:$C$100="OTN"))
Old & OT7 =SUMPRODUCT(($B$2:$B$100="Old")*($D$2:$D$100="OT7"))
New & OTN =SUMPRODUCT(($A$2:$A$100="New")*($C$2:$C$100="OTN"))
New & OT7 =SUMPRODUCT(($A$2:$A$100="New")*($D$2:$D$100="OT7"))


Report •

June 7, 2010 at 06:42:58
Thank you so much! This formula works perfectly!

Report •

Related Solutions

Ask Question