+ Reply to Thread
Results 1 to 11 of 11

sumproduct...into buckets

  1. #1
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,191

    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.
    Attached Files Attached Files
    Last edited by jw01; 03-08-2011 at 08:29 PM.

  2. #2
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: sunproduct...into buckets

    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,
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  3. #3
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,191

    Re: sumproduct...into buckets

    hello

    thx u for your feedback and help with this matter!

    the countif appears to be functioning correctly so thx you

    just a quick question. the last formula, for the national code locations

    National Account
    =COUNTIF('Max Consoles'!$V$2:$V$2000,"<>"&"")

    that is returning all entries for some reason?...in my workbook, the max consoles has 54271 entries, and not all are national accounts; but when i run this formula, its showing 54,271 as an answer.

    would you have any suggestions? i really appreciate your feedback!..

  4. #4
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: sumproduct...into buckets

    Oops! Does this work?

    Please Login or Register  to view this content.
    Cheers,

  5. #5
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,191

    Re: sumproduct...into buckets

    wow thx u for your prompt response, it is much appreciated!

    that definitely helped bc now my list is down to 207 missing accounts and im assuming those are bc of 0?

    also, another quick question, i also have on another sheet their revenue, can i simply just use my sumproduct function for that? thxs alot!

  6. #6
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: sumproduct...into buckets

    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,

  7. #7
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,191

    Re: sumproduct...into buckets

    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.
    Last edited by jw01; 03-09-2011 at 10:17 AM.

  8. #8
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,191

    Re: sumproduct...into buckets

    Hello

    i have tried to post a bigger file (see attached). although it does not have all 54k entries, it has 7k...but if i sum all the countif entries on the summary tab, ...the countif count is over by 46 entries.

    also, i tried to use the sumproduct fucntion in the cell beside it (range G7:G10 - summary worksheet)...their lookup is embedded in the max console page in range X-Y.

    kindly let me know your thoughts. thx u!
    Attached Files Attached Files

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: sumproduct...into buckets

    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.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  10. #10
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,191

    Re: sumproduct...into buckets

    hello

    for some odd reason, after placing those entries in my main file and changing the range from $7000 to $54271 for all....im getting a larger variance.

    i.e. total count of locations 1 all the way to national account is 52,539.
    -total entries is 54271. so thats leaving 1732 entries that did not get picked up. the formulas i had before gave me a variance of 209.

    the variance is in the 2-19 bucket (service locations between 2 and 19).
    your formula says ">19"...but shouldnt it be "=19"?? and for the sumproduct, is there something that im doing completely wrong? ...again much appreciated. thx u so much!

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: sumproduct...into buckets

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1