asked on

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!

ASKER CERTIFIED SOLUTION

NBVC

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NBVC

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)

barry houdini

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

crepe

ASKER

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!!

crepe

ASKER

Thank you!