Sumproduct Help

BrianM

Well-known Member
Joined
Jan 15, 2003
Messages
764
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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
Joined
Jan 15, 2003
Messages
764
Thanks PaddyD, works fine.
Could you please explain the difference in the two formulas

Thanks
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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
Joined
Jan 15, 2003
Messages
764

ADVERTISEMENT

How might this formula be modified to take into account any blank cells??

Thanks,
Brian
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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
Joined
Jan 15, 2003
Messages
764
Absolutely does. I figured out what I did wrong.

Thanks again,

Brian
 

bosco_yip

Well-known Member
Joined
Dec 2, 2002
Messages
1,938
Office Version
  1. 2019
Platform
  1. Windows
Or try another way , in Cell D3 enter :

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


HTH

Regards
 

Watch MrExcel Video

Forum statistics

Threads
1,127,867
Messages
5,627,354
Members
416,244
Latest member
JBKool

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
Top