SUMPRODUCT with Wildcard
 

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

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




"JerryS" wrote:

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

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

--
Don Guillett
SalesAid Software

"JerryS" wrote in message
...
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
--
JerryS

"Jason Morin" wrote...
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.

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

On Sat, 11 Jun 2005 05:26:02 -0700, JerryS
wrote:

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

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


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

--
Don Guillett
SalesAid Software

"Maxwell" wrote in message
...
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

On Sat, 11 Jun 2005 05:26:02 -0700, JerryS
wrote:

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

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

On Thu, 16 Jun 2005 11:13:53 -0500, "Don Guillett"
wrote:

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


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

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


--
Don Guillett
SalesAid Software

"Maxwell" wrote in message
...
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

On Thu, 16 Jun 2005 11:13:53 -0500, "Don Guillett"
wrote:

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


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