Multi-criteria Counting (array formulas)

**CWIS** · 12-14-2009, 10:49 PM

I am having a major issue in getting an array formula to work for in counting multiple criteria. I am looking for the intersection of two types of data. One column (E2:E413) has data that has been coded 1,2,3, or 4. Another column (T2:T413) has data that has been coded 1,2,3,4,5,7, or 8. I am trying to count (not sum) how many times that both 1 shows up in column E AND 1 shows up in column T.
I have tried many different formulas, but I'm a novice at Excel and am need of help. The only familiarity I have with this program comes from the misery I've had with it in the last few days. Below are two of formulas that I have tried (found them via web), but to no avail:

=SUM(IF($E$2:$E$120="1",IF($T$2:$T$120="1",1,0),0))
=SUM(($E$2:$E$413="1")*($T$2:$T$413="1"))

If anyone can help, I would be so super appreciative.

Thanks,
Tiffany

**davesexcel** · 12-14-2009, 10:53 PM

Check out this link on SumProduct
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

**JieJenn** · 12-15-2009, 01:16 AM

Use CountIf function

Since I don't have the source File I can only assume
In Column A and Column E you have the data listed.

So if will probably look like this

=sum(countif(A:A,1),countif(E:E,1)

this will give you how many 1s show up in both column a and column e

**DonkeyOte** · 12-15-2009, 03:20 AM

Tiffany, given you're use of 2008 you can use the new COUNTIFS function which is more efficient than SUMPRODUCT

=COUNTIFS($E$2:$E$413,1,$T$2:$T$413,1)

If backwards compatibility (say 2005 etc) then you would use either SUMPRODUCT or Array but SUMPRODUCT is perhaps a little more robust from ane end-user perspective in so far as it does not require "special entry" - ie it can be committed with ENTER as per other formula

=SUMPRODUCT(--($E$2:$E$413=1),--($T$2:$T$413,1))

The suggestion in the prior post won't work in this instance given it will (once corrected re: parenthesis) simply give you a count of the 1's appearing in either/or column rather than in both columns simultaneously.

**CWIS** · 12-15-2009, 03:48 PM

Thank you SO so much DonkeyOte!! I used the =COUNTIFS($E$2:$E$413,1,$T$2:$T$413,1) formula and it worked beautifully.
So incredibly appreciative, totally made my day!!!!