question about SUMPRODUCT

N

nmc1104

I have a worksheet laid out like this

COLUMN A COLUMN B
manager present
supervisor absent
supervisor present
CEO absent
manager absent

How would I write a formula that would count every "manager" and
"supervisor" present?

thanks.
 
D

Domenic

Try...

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A5,{"Manager","Supervisor"},0))),--(B1:B
5="Present"))

or

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A5,D1:D2,0))),--(B1:B5=E1))

....where D1:D2 contain Manager and Supervisor, and E1 contains Present.

Hope this helps!
 
B

Bernard Liengme

Try =SUMPRODUCT(--(A1:A100="manager"),--(B1:B100="supervisor"))
The double negatives convert FALSE/TRUE to 0/1 to allow arithmetic to work
best wishes
 
P

Peo Sjoblom

Assuming "and" means or in this case

=SUMPRODUCT((A1:A5={"manager","supervisor"})*(B1:B5="present"))
 
N

nmc1104

thanks! you guys are pros.

Peo Sjoblom said:
Assuming "and" means or in this case

=SUMPRODUCT((A1:A5={"manager","supervisor"})*(B1:B5="present"))


--

Regards,

Peo Sjoblom
Click to expand...
 
Top