sumproduct...into buckets

Hello

I have a pool of data that needs to be placed in buckets
i.e.
1 (should not include National)
2-19 (should not include National)
20+ (should not include National)
National

i used the sum product in my range but it's not working and giving me the incorrect results ( im pretty sure).

this is my formula in cell F8

=SUMPRODUCT(--('Max Consoles'!$U$2:$U$54272>=2),--('Max Consoles'!$U$2:$U$54272<=19),'Max Consoles'!$U$2:$U$54272)

basically....this is being applied for the bucket 2-19
-im trying to figure out from my data, the consoles that are in 2-19 range, that are also not coded national. im using column "U" in max consoles worksheet to show non national codes.

if someone can kindly and pls help me that would be great thx u.

If you're trying to count the number of service locations, these formulas might work better for you:

Please Login or Register  to view this content.

Cheers,

Oops! Does this work?

Please Login or Register  to view this content.

Cheers,

I only see 22 zeros in your non-national sample list. Are you working in your full file?

I can't see the revenue either, but your SUMPRODUCT functions should probably work for those. If they don't, you can always post another thread in this forum!

Cheers,

Hello

quick question, what exactly is the "*" portion in the national code searching for?

and bc i have 207 entries that didnt get captured (assuming they are 0's) can i just use a normal formula to search for 0? to see if it comes around 207?

ie.

Please Login or Register  to view this content.

thx you again! much appreciated.

and just real quick, the portion where it's
2-19 (should not include National)
that formula implies to search between 2 to 19, correct? not 2 and 19 only?

Please Login or Register  to view this content.

Try formulas in column F:

=COUNTIF('Max Consoles'!$U$2:$U$7000,1)

=COUNTIF('Max Consoles'!$U$2:$U$7000,">=2")-COUNTIF('Max Consoles'!$U$2:$U$7000,">19")

=COUNTIF('Max Consoles'!$U$2:$U$7000,">=20")

=COUNT('Max Consoles'!$V$2:$V$7000)

respectively.

You had the COUNTIF('Max Consoles'!$U$2:$U$7000,">19") part wrong.

Are you counting in column U or column V?

I am not sure where the issue is?

The 2-19 bucket should be as >19

the first COUNTIF counts all that is greater than or equal to 2, then it substracts the second Countif which is all records greater than 19... leaving you with all records in between, i.e. 2 to 19.


The sumproduct formulas can/should be changed to similar CoUNTif formulas as they are more efficient.