Multiple Sumif (Criteria within same column)

[thanksinadvance]

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. (e.g. =SUMIF(A2:A13,OR("Apple","Banana","Cherry"),(F2:F13))

Please help!!

(I want to upload a sample attachment .xls but have no idea how to )


Criteria MAY-11 JUN-11 JUL-11 AUG-11 SEP-11
Apple 0.00 0.00 (0.00) (0.00) (52.15)
Banana 3.00 5.00 2.00 0.00 (2.05)
Cherry 34.00 2.00 23.00 21.00 0.00
Ford 0.00 0.00 0.00
Porsche 63.80 74.44 74.44
Mango 0.00 10.63 (242.27) (472.00)
Straberry 107.14 960.00 200.00
Toyota 0.00 0.00 0.00
Nissan 23.68 (3.25) (3.25)
Honda 366.64 (914.50) (15.50)
Carrot 0.00
Bean (351.52) 1,047.75 (0.00)




Desired End Result
Criteria MAY-11 JUN-11 JUL-11 AUG-11 SEP-11
Apple 37.00 7.00 25.00 21.00 (54.20)
Ford 0.00 0.00 0.00
Porsche 63.80 74.44 74.44
Mango 0.00 0.00 117.77 717.73 (272.00)
Toyota 0.00 0.00 0.00
Nissan 23.68 (3.25) (3.25)
Honda 366.64 (914.50) (15.50)
Carrot 0.00 0.00 (351.52) 1,047.75 (0.00)

 

[Dannyh1]

Hi

try sumproduct e.g:

=SUMPRODUCT((A2:A13={"Apple","Banana","Cherry"})*F2:F13)

 

[thanksinadvance]

Hi Danny,

Thanks for the really quick reply

Your formula works! It allows me to combine the values of all 3 (Apple, Banana, Cherry i.e. ABC), and I'm sorry if I wasn't clear but the thing is, I also need to combine the Criteria categories (ABC) into just A in a separate sheet/area (though this A should reflect the summed ABC values for that particular month).

Using the syntax you provided, I can now do a vlookup (=VLOOKUP({"Apple","Banana","Cherry"},'Raw Data'!$A:$AA,6,FALSE)) to combine the criteria ABC into A for the Criteria of a new table, but still am not sure how to combine the values with Sumif so that the combined "ABC" values are reflected as an "A" value in the new table.

Is there any way I can attach an .xls file to show you an example?

The below is what I mean, in the meanwhile:


Criteria MAY-11 JUN-11 JUL-11 AUG-11 SEP-11
Apple 0.00 0.00 (0.00) (0.00) (52.15)
Banana 3.00 5.00 2.00 0.00 (2.05)
Cherry 34.00 2.00 23.00 21.00 0.00


...to become...


Criteria MAY-11 JUN-11 JUL-11 AUG-11 SEP-11
Apple 37.00 7.00 25.00 21.00 (54.20)