Multiple Sumif (Criteria within same column)

1. 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.

2. 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...

3. 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!

4. 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. 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. 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. 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.

8. 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).

9. 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. Re: Multiple Sumif (Criteria within same column)

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

11. 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.

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

12. Re: Multiple Sumif (Criteria within same column)

Ok, thanks!

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