# Excel Sumproduct to Count multiple column criterias once per row and only 2 rows max

Good morning,

I have a table like the following:

A                   B         C         D           E
Equipment   Time   Time   Time  Running
A                    4        5         4         Yes
A                    5        0         0         Yes
A                    6        0         5         Yes
A                    0        5         4         Yes
B                    0        0         0          No

I want to Count the "0" only once per row. I tried using
=SUMPRODUCT((E2:E6="Yes")*(B2:D6=0))
But I get "4" and I only want "3".

I tried
=SUMPRODUCT((E2:E6="Yes")*((B2:B6=0)+(C2:C6=0)+(D2:D6=0)))
And I get "4"

Then, I want to only count 2 out of the 4 Equipment reps if there are multiple "0" in a row.
So I would get "2" and not "3".

Thank you!
###### Who is Participating?

Commented:
Try:

=SUMPRODUCT(--(MMULT(--(\$B\$2:\$D\$6=0),{1;1;1})>0),--(\$E\$2:\$E\$6="Yes"))
0

Commented:
For the 2nd part try:

=COUNT(1/FREQUENCY(IF(SUMPRODUCT(--(MMULT(--(\$B\$2:\$D\$6=0),{1;1;1})>0)),IF(\$A\$2:\$A\$6<>"",MATCH(\$A\$2:\$A\$6,\$A\$2:\$A\$6,0))),ROW(\$A\$2:\$A\$6)-ROW(\$A\$2)+1))

confirmed with CTRL+SHIFT+ENTER not just ENTER
(it's an Array formula)
0

Commented:
NB_VC's suggestion is more easily extensible for multiple columns but for your first question you could amend your suggested formula slightly, i.e.

=SUMPRODUCT((E2:E6="Yes")*((B2:B6=0)+(C2:C6=0)+(D2:D6=0)>0))

The >0 part ensure that you only count each row once at most....

I'm not clear on the second part - how do you get 2?

If you simply never want > 2 then try

=MIN(2,SUMPRODUCT((E2:E6="Yes")*((B2:B6=0)+(C2:C6=0)+(D2:D6=0)>0)))

regards, barry
0

Author Commented:
The 1st part worked beautifully!

The 2nd part doesn't work when I add it to my table. I added the following:
A                   B         C         D           E
Equipment   Time   Time   Time  Running
A                    4        5         4         Yes
A                    5        0         0         Yes
A                    6        0         5         Yes
A                    0        5         4         Yes
B                    0        0         0          No
C                    0        0         2         Yes
C                    0        4         5          Yes
C                    3        0         3          Yes
C                    4        0         5          Yes

I used:
=COUNT(1/FREQUENCY(IF(SUMPRODUCT(--(MMULT(--(\$B\$2:\$D\$6=0),{1;1;1})>0)),IF(\$A\$2:\$A\$6<>"",MATCH(\$A\$2:\$A\$6,\$A\$2:\$A\$6,0))),ROW(\$A\$2:\$A\$6)-ROW(\$A\$2)+1))
with CTRL+SHIFT+ENTER

And I get "3" and I need "4".

I'm going to close this question and start a new question on the 2nd part with modifications. Thank you so much!!
0

Author Commented:
Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.