+ Reply to Thread
Results 1 to 12 of 12

Multiple Sumif (Criteria within same column)

  1. #1
    Registered User
    Join Date
    07-19-2011
    Location
    Asia
    MS-Off Ver
    Excel 2003
    Posts
    10

    Angry Multiple Sumif (Criteria within same column)

    Hi,

    Am new to this so would greatly appreciate any help (and soon, please!)

    Basically I need to constantly pull data from a database which will be exported to .xls in the Cell Range A1:F13 of the attached. (This will typically be much longer in detail).

    I need to process the raw data to get summed, monthly data by the various categories as seen in the "Desired End Result" table.

    The problem is, out of the exported data, there are groups (renamed to fruits and vegetables and highlighted) for which I need to sum the data (e.g. Apple+Banana+Cherry, summed to Apple, by month) BUT the search criteria is within the same column

    I am using Excel 2003 and so do not have Sumifs (wouldn't know how to use it anyway). I have been trying to combine =Sumif and =Or but have come to realise Sumif can't work for multiple conditions.

    Please help!!
    Attached Files Attached Files
    Last edited by Thanksinadvance; 07-20-2011 at 09:34 AM.

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

    Re: Multiple Sumif (Criteria within same column)

    Try:

    =SUMPRODUCT(SUMIF($A$2:$A$13,{"Apple","Banana","Cherry"},B$2:B$13))

    copied across the row.

    and similar for other categories...
    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.

  3. #3
    Registered User
    Join Date
    07-19-2011
    Location
    Asia
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Multiple Sumif (Criteria within same column)

    Hi NBVC,

    Thanks for your really prompt response. Your formula works! unfortunately, I'm sorry if I wasn't clear but because I need the raw data processed into a different sheet, combining the values of Apple + Banana + Cherry into just one for Apple (in the corresponding month), is there any way I can do this?

    Have included the attached .xls which I hope illustrates my point. Using your syntax {} (does this work for all references, replaceing =OR?), I can now vlookup a combination of Apple Banana Cherry into the Apple, but only for Apple's value (instead of the combined A+B+C),

    Thanks once again and hope to hear from you soon!
    Attached Files Attached Files

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

    Re: Multiple Sumif (Criteria within same column)

    You can reference other sheet,

    eg, in F2 of End Table sheet:

    =SUMPRODUCT(SUMIF('Raw Data'!$A$2:$A$13,{"Apple","Banana","Cherry"},'Raw Data'!F$2:F$13))

  5. #5
    Registered User
    Join Date
    07-19-2011
    Location
    Asia
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Multiple Sumif (Criteria within same column)

    Sorry, do you mean I have to hardcode "{"Apple","Banana","Cherry"}" in each cell of the new table in End Table sheet?

    I mentioned combining a vlookup and Sumif formula because the actual table I'm doing this for stretches into 40-50 rows, so in effect I need to: (i) condense any ABCs into just A, (ii) sum the indiv ABC values into a lump sum for A in the new table.

    Is there any way for a formula to look like that?

    =SUMPRODUCT(SUMIF('Raw Data'!$A$2:$A$13,VLOOKUP({"Apple","Banana","Cherry"},'Raw Data'!$A:$AA,6,FALSE),'Raw Data'!F$2:F$13))

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

    Re: Multiple Sumif (Criteria within same column)

    Sorry not sure what you mean... your vlookup has the items hardcoded? Do you mean you want to the column number to be dynamic?

    Can you post a more realistic example and show what you really want? I am confused about your VLOOKUP portion.

  7. #7
    Registered User
    Join Date
    07-19-2011
    Location
    Asia
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Multiple Sumif (Criteria within same column)

    Ok reattached the xls so that hopefully I can better illustrate what I mean. Effectively, out of the raw data, I'm hoping to combine certain values (A+B+C) under certain categories (A+B+C to be combined into just A) into a new table.

    There are a lot of categories (40-50, with potentially more), so when you said in your previous response I could "reference other sheet", did you mean that for each individual Criteria, I will need to amend the "Apple/Banana/etc" into "Mango + Strawberry", etc?

    The raw data that will be exported and pasted into the "Raw Data" sheet will vary in it's criteria (Apple, etc), period (Aug-11, etc) and values, hence my query as to whether I could map the raw data into the End Sheet with a vlookup or something similar which can automatically pick up and process these changes.
    Attached Files Attached Files

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

    Re: Multiple Sumif (Criteria within same column)

    Not sure if this helps, but here is what I did in the attached.

    I created a table in Raw Data, O1:Q3 of all the categories that have subcategories. I named each of these ranges by selecting each range individually and entering the main header value as a name in the Name Box just to the left of the formula bar.

    E.g. Selected O1:O3 and in the Name Box, entered Apple and repeated for P1:P2 and Carrot and O1:O2 and Mango

    Then formula in End Table sheet, B2 is:

    Please Login or Register  to view this content.
    which references indirectly the named ranges to find the subcategories to include in the summation.

    Copied down and across the matrix.

    Adjust ranges to suit... but no whole columns allowed (minimize number of rows in ranges to avoid efficiency issues).
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-19-2011
    Location
    Asia
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Multiple Sumif (Criteria within same column)

    Excellent! Thanks, NBVC, I think I understand your code. You basically check the refence value in Column A against the Named Values, then sum the values if they are in a named group, or just the individual value if it's not?

    Tried playing around with this, but realised that it would have been cleaner if I used your previous Sumproduct(Sumif) formula and amended the "Apple"(etc) details.

    Thanks a lot! Have separately PM-ed you.

    Could I lastly just find out about the use of "{}", and "--" (as you did before the ISNUMBER), so that I can maybe adapt it for future use?

  10. #10
    Registered User
    Join Date
    07-19-2011
    Location
    Asia
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Multiple Sumif (Criteria within same column)

    Also, how do I mark this thread [Solved] ?

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

    Re: Multiple Sumif (Criteria within same column)

    The { } defines an array of values... in this case it only works because we are adding the SUMPRODUCT() around the SUMIF() to get excel to SUMIF on each array criteria and then sum together... The {} is a bit tricky sometimes and you kind of have to know how/when it will work.

    The -- is called a double unary and it is used to convert TRUE/FALSE results created from the ISNUMBER() condition, into a 1/0 array so that Sumproduct can do the math.

    Here is a good Sumproduct article explaining the double unary and more about this powerful function.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

  12. #12
    Registered User
    Join Date
    07-19-2011
    Location
    Asia
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Multiple Sumif (Criteria within same column)

    Ok, thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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