SUMPRODUCT with Wildcard

[Guest]

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

 

[Guest]

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

 

[Don Guillett]

try
=SUMPRODUCT((LEFT(J1:J5)="2")*1)

 

[Harlan Grove]

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

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.

 

[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

 

[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

 

[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

 

[Don Guillett]

Did not see any attachments and we DISCOURAGE attachments here.
I use 2002.