Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 427
  • Last Modified:

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!
0
crepe
Asked:
crepe
  • 2
  • 2
1 Solution
 
NBVCCommented:
Try:

=SUMPRODUCT(--(MMULT(--($B$2:$D$6=0),{1;1;1})>0),--($E$2:$E$6="Yes"))
0
 
NBVCCommented:
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
 
barry houdiniCommented:
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
 
crepeAuthor 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
 
crepeAuthor Commented:
Thank you!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now