SUMPRODUCT with Wildcard

J

JerryS

How do I set up a sumproduct formula to count everything in a column that
starts with the number 2. All of the numbers are 3 digits long. Thanks
 
J

Jason Morin

There are several ways:

=COUNT(A:A)-SUM(COUNTIF(A:A,{"<200",">299"}))
=SUM(IF(ISNUMBER(A1:A100),IF(LEFT(A1:A100)="2",1)))
=SUMPRODUCT((A1:A100>=200)*(A1:A100<=299))

The 2nd formula is an array formula, so press ctrl + shift + enter for it to
work.

HTH
Jason
Atlanata, GA
 
H

Harlan Grove

Jason Morin said:
There are several ways:

=COUNT(A:A)-SUM(COUNTIF(A:A,{"<200",">299"}))
=SUM(IF(ISNUMBER(A1:A100),IF(LEFT(A1:A100)="2",1)))
=SUMPRODUCT((A1:A100>=200)*(A1:A100<=299))
Click to expand...

With regard to the first, why not

=COUNTIF(A:A,">=200")-COUNTIF(A:A,">=300")

With regard to the third, even though the OP stated all numbers are 3-digit,
it's still safer to use >=200 and <300.
 
M

Maxwell

You need to be careful when using the LEFT function in this way. While
the numbers are all three digits, some could have leading zeros (if
formatted that way). The LEFT function returns the leftmost
significant digits, and will exclude leading zeros. Therefore, it will
return "2" for either "250" or "024", and potentially overcount your
start-with-2's.

Just my 2 cents.


Seth
 
D

Don Guillett

=SUMPRODUCT((LEFT(E2:E5)="2")*1)
222
0235
00025
00250


number
text
formatted with leading 0's
ans is 1 for the 222
 
M

Maxwell

Don, I get the attached results uing both your and HTH's formulas that
rely on LEFT. I'm running Excel 2003. What version are you using?


Seth
 
Top