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!

Hi 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

=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 OTN

Andrew

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 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"))Regards

Thank you so much! This formula works perfectly!

Ask Your Question

Weekly Poll

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History