# Sumproduct Help

#### BrianM

##### Well-known Member
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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

##### MrExcel MVP
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

#### BrianM

##### Well-known Member
Thanks PaddyD, works fine.
Could you please explain the difference in the two formulas

Thanks

##### MrExcel MVP
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.

#### BrianM

##### Well-known Member
How might this formula be modified to take into account any blank cells??

Thanks,
Brian

##### MrExcel MVP
depends what you mean - under the most simle interpretation, it already does:
Book1
ABCD
1DataFindCount
2CRR123456CRR2
3CRR789456
4CRS123456
5
6CRT123456
Sheet1

#### BrianM

##### Well-known Member
Absolutely does. I figured out what I did wrong.

Thanks again,

Brian

#### bosco_yip

##### Well-known Member
Or try another way , in Cell D3 enter :

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

HTH

Regards

Replies
9
Views
491
Replies
0
Views
216
Replies
5
Views
263
Replies
7
Views
372
Replies
8
Views
2K

1,171,064
Messages
5,873,579
Members
432,985
Latest member
leahw

### 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?

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