Using sumproduct to count entries with multiple criteria.

J

Jaard

New Member
Joined
Apr 17, 2006
Messages
3
I want to count the number of entries which meet multiple criteria, and thought I was on the right track with SUMPRODUCT.

A B C D

5 C GPH/CCU 18 Y
6 C GPH/EC 8 Y
7 H SU/C 25 N
8 F GPH/43 0 N
9 C GPH/31 31 N

the criteria i want met are
A5:A200 = "C"
B5:B200 = anything starting with GPH
C5:C200 >15
D5:D200 ="Y"

I tried sumproduct(($A$5:$A$200="C")*($B$5:$B$200="GPH*")*($C$5:$C$200>15)*($D$5:$D$200="Y"))
without much success so far.
Thanks for any help.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Try:

=SUMPRODUCT(($A$5:$A$200="C")*(ISNUMBER(SEARCH("GPH",$B$5:$B$200))*($C$5:$C$200>15)*($D$5:$D$200="Y")))
 
Upvote 0
Re: Using sumproduct to count entries with multiple criteria

I want to count the number of entries which meet multiple criteria, and thought I was on the right track with SUMPRODUCT.

A B C D

5 C GPH/CCU 18 Y
6 C GPH/EC 8 Y
7 H SU/C 25 N
8 F GPH/43 0 N
9 C GPH/31 31 N

the criteria i want met are
A5:A200 = "C"
B5:B200 = anything starting with GPH
C5:C200 >15
D5:D200 ="Y"

I tried sumproduct(($A$5:$A$200="C")*($B$5:$B$200="GPH*")*($C$5:$C$200>15)*($D$5:$D$200="Y"))
without much success so far.
Thanks for any help.
Click to expand...

Try,

=SUMPRODUCT(--(LEFT(B5:B200,3)="GPH"),--(A5:A200="C"),--(C5:C200>15),--(D5:D200="Y"))
 
Upvote 0
Thanks, this worked great in a test sheet. But when i adapted the ranges to suit my sheet, it returned a value of 0.
UrgentReport.xlt
ABCDEFGHIJK
21CN562849130GPH/CCUC130(06:46)5:005:59596:46472
22CN562892457MP/8EC453(10:27)5:008:5723710:279045
23CN562892494MP/8NC474(07:19)5:005:34347:1910560
24CN562102424GPH/CCUC130(06:57)5:055:57526:576015
Episodes

Would I be better off using autofilter? How can i count the visible results of an autofilter operation?.
NB. with this table criteria is A:A=C B:B=Y C:C=GPH* K:K>15
 
Upvote 0
Thanks, this worked great in a test sheet. But when i adapted the ranges to suit my sheet, it returned a value of 0.
Click to expand...

What is the adapted formula you implemented?

...

Would I be better off using autofilter? How can i count the visible results of an autofilter operation?.
NB. with this table criteria is A:A=C B:B=Y C:C=GPH* K:K>15
Click to expand...

=SUBTOTAL(3,$B$2:$B$400)

Adjust the range to suit.
 
Upvote 0
the adapted formula i tried is
=SUMPRODUCT(($A$5:$A$2000="C")*(ISNUMBER(SEARCH("GPH",$C$5:$C$2000))*($K$5:$K$2000>15)*($B$5:$B$2000="Y")))
 
Upvote 0
the adapted formula i tried is
=SUMPRODUCT(($A$5:$A$2000="C")*(ISNUMBER(SEARCH("GPH",$C$5:$C$2000))*($K$5:$K$2000>15)*($B$5:$B$2000="Y")))
Click to expand...

Firstly, try to eliminate possible stray spaces around the data entries with the TrimAll macro, the code of which is available in some of the threads on this board.

Secondly, since you have a "starts with" condition, invoke a SumProduct formula of the type Brian suggested:

=SUMPRODUCT(--($A$5:$A$2000="C"),--($B$5:$B$2000="Y"),--(LEFT($C$5:$C$2000,3)="GPH"),--($K$5:$K$2000>15))
 
Upvote 0
the adapted formula i tried is
=SUMPRODUCT(($A$5:$A$2000="C")*(ISNUMBER(SEARCH("GPH",$C$5:$C$2000))*($K$5:$K$2000>15)*($B$5:$B$2000="Y")))
Click to expand...


In case there are spaces, instead of including macros in your sheet, you can also adapt my formula to "trim" for you..


=SUMPRODUCT((TRIM($A$5:$A$2000)="C")*(ISNUMBER(SEARCH("GPH",$C$5:$C$2000))*(($K$5:$K$2000)+0>15)*(TRIM($B$5:$B$2000)="Y")))
 
Upvote 0
There is no need to include additional function calls (like TRIM) in a formula that is already resource-demanding. Run the TrimAll macro then remove it from your workbook if needed. Moreover, invoking SEARCH is risky if the condition "begins with" is all that is needed.
 
Upvote 0
As alluded to by Aladin SUMPRODUCT is resource hungry.

If you have any reasonable amount of data, consider other options. Especially a pivot table, depending on what you'e doing. Filtering might also be suitable. There are other ways too.

Fazza
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
I'm trying to count cells that meet 2 criteria using CountIfs
Replies
3
Views
274
kevin9999
K
B
  • Question
VBA code that uses if/then and multiple sheets
Replies
2
Views
298
BAQI
B
R
  • Question
Help! VBA to loop through a list containing data references to update forecast sheets automatically
Replies
3
Views
154
Domenic
D
E
  • Solved
Find "nth" input, whilst using a filter
Replies
2
Views
127
ellison
E
E
  • Solved
COUNTIF and SUMIF formula with multiple criteria
Replies
2
Views
162
excelnewb221
E

Forum statistics

Threads
1,215,433
Messages
6,124,861
Members
449,194
Latest member
HellScout

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top