Need a formula for counting all the numbers starting with a 4 within a range of 1 to 100,000.
D dbglass May 11, 2006 #1 Need a formula for counting all the numbers starting with a 4 within a range of 1 to 100,000.
P Peo Sjoblom May 11, 2006 #2 One way =SUMPRODUCT(--(LEFT(A1:A10000)="4")) or if you meant that the range of numbers can be greater but you don't want to count numbers starting with 4 that are greater than 100,000 =SUMPRODUCT(--(LEFT(A1:A10000)="4"),--(A1:A10000<=100000)) -- Regards, Peo Sjoblom http://nwexcelsolutions.com
One way =SUMPRODUCT(--(LEFT(A1:A10000)="4")) or if you meant that the range of numbers can be greater but you don't want to count numbers starting with 4 that are greater than 100,000 =SUMPRODUCT(--(LEFT(A1:A10000)="4"),--(A1:A10000<=100000)) -- Regards, Peo Sjoblom http://nwexcelsolutions.com
D dbglass May 12, 2006 #3 Thank you, it works! dbglass Peo Sjoblom said: One way =SUMPRODUCT(--(LEFT(A1:A10000)="4")) or if you meant that the range of numbers can be greater but you don't want to count numbers starting with 4 that are greater than 100,000 =SUMPRODUCT(--(LEFT(A1:A10000)="4"),--(A1:A10000<=100000)) -- Regards, Peo Sjoblom http://nwexcelsolutions.com Click to expand...
Thank you, it works! dbglass Peo Sjoblom said: One way =SUMPRODUCT(--(LEFT(A1:A10000)="4")) or if you meant that the range of numbers can be greater but you don't want to count numbers starting with 4 that are greater than 100,000 =SUMPRODUCT(--(LEFT(A1:A10000)="4"),--(A1:A10000<=100000)) -- Regards, Peo Sjoblom http://nwexcelsolutions.com Click to expand...
