# Count if with multiple values

Microsoft Office 2000 standard
June 4, 2010 at 13:41:22
Specs: Microsoft Excel 2000
 Hi, 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

#1
June 4, 2010 at 14:59:37
 Hiyou need an array formulasupposing 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=SUM((A2:A123="Yes")*(C2:C123="Yes")*1)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 OTNAndrew

Report •

#2
June 5, 2010 at 05:45:06
 Hi,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 G5You 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 blankCol. B Old or blankCol. C OTN or blankCol. D OT7 or blankThen 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"))Regards

Report •

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

Report •

Related Solutions