Sumproduct Help

BrianM

Well-known Member
Joined
Jan 15, 2003
Messages
768
Office Version
  1. 2016
Platform
  1. Windows
Can anyone explain what I'm doing wrong, or suggest another way to accomplish this? I want to count the number of times CRR, 2HS, etc. are in column A:A without having to text to columns in order to separate then count. D3 is the formula I'm currently working on.

Thanks

Brian
Book1
ABCD
1LocationJan. 5-9
22HS 50289Random BinsQuants
32HS 50229CRRno0
4SHS 408682HR
5CRR 003DSHS
6CSR 151E
7CRR 003L
82HS 70149
9SHS 30148
10SHS 40138
Sheet1
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
just need an extra step to coerce the logicals to numerics (so that sumproduct has something to sum):
Book1
ABCD
1DataFindCount
2CRR123456CRR2
3CRR789456
4CRS123456
Sheet1
 
Upvote 0
Thanks PaddyD, works fine.
Could you please explain the difference in the two formulas

Thanks
 
Upvote 0
given ny example above:

=SUMPRODUCT((LEFT(A2:A4,3)=C2))

evaluates to {TRUE;TRUE;FALSE}

...which is interesting, but useless for sumproduct, as sumproduct can only sum numeric values. so, we need to coerce the logicals to numerics. the basic method to do this is to perform a mathematical operation on the array, so any of:

=SUMPRODUCT(--(LEFT(A2:A4,3)=C2))
=SUMPRODUCT((LEFT(A2:A4,3)=C2)*1)
=SUMPRODUCT((LEFT(A2:A4,3)=C2)+0)

would do. the -- option is preferred by some as it is more efficient (albeit marginally so). after the coersion, the array is:

{1;1;0}

...which, as a numeric array, can be fed to the sumproduct to give the answer.
 
Upvote 0
How might this formula be modified to take into account any blank cells??

Thanks,
Brian
 
Upvote 0
depends what you mean - under the most simle interpretation, it already does:
Book1
ABCD
1DataFindCount
2CRR123456CRR2
3CRR789456
4CRS123456
5
6CRT123456
Sheet1
 
Upvote 0
Absolutely does. I figured out what I did wrong.

Thanks again,

Brian
 
Upvote 0
Or try another way , in Cell D3 enter :

=COUNTIF($A$2:$A$10,B3&"*")


HTH

Regards
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

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