Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

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

Posted on 2013-05-16
Medium Priority
424 Views
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
Question by:crepe
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 23

Accepted Solution

NBVC earned 2000 total points
ID: 39171717
Try:

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

LVL 23

Expert Comment

ID: 39171809
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

LVL 50

Expert Comment

ID: 39172774
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 Comment

ID: 39188608
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 Closing Comment

ID: 39188609
Thank you!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month9 days, 10 hours left to enroll