Thread: 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!!

Try:

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

copied across the row.

and similar for other categories...

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

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.

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:

=IF(ISNUMBER(MATCH($A2,'Raw Data'!$O$1:$Q$1,0)),SUMPRODUCT(--(ISNUMBER(MATCH('Raw Data'!$A$2:$A$13,INDIRECT($A2),0))),'Raw Data'!B$2:B$13),SUMIF('Raw Data'!$A$2:$A$13,$A2,'Raw Data'!B$2:B$13))

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

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