# 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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,018
Messages
5,856,840
Members
431,836
Latest member
jj0911

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

### Which adblocker are you using?

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

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