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

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

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
768
Office Version
  1. 2016
Platform
  1. Windows
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
768
Office Version
  1. 2016
Platform
  1. Windows

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
768
Office Version
  1. 2016
Platform
  1. Windows
Absolutely does. I figured out what I did wrong.

Thanks again,

Brian
 

bosco_yip

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

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


HTH

Regards
 
Master Excel Bundle

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.

Forum statistics

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