Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
"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. |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
=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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct Wildcard | Excel Discussion (Misc queries) | |||
Sumproduct with Wildcard * | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |