SUMIF with multiple criteria

[Piraneous]

Hi,

i'm trying to make my spreadsheet add values in a column if it matches 2 criteria. before i've used sumif, but this only works with one criteria.

the spreadsheet has 3 columns i'm interested in

Name | Type | Quantity
-----------------------------------
Joe | A | 100
Ben | B | 20
Joe | B | 50

name being a persons name, Type being type of material, and quantity being quantity produced.

I need a way of adding all the quantitys that "joe" has made of each type. Hopefully something simple that my poor self taught Excel skills can handle.

hope that makes some kind of sense

[NBVC]

Try Sumproduct

e.g. =Sumproduct(--(Range1=Criteria1),--(Range2=Criteria2),SumRange)

[Piraneous]

hmm... not sure i'm putting it in correctly. the excel help doesn't really give me much guidance as to the formatting to suit. i'm just getting errors... any chance you could type it out in the exact syntax so all i have to do is change the Range1 Range2 Criteria 1&2 and sum range values?

[NBVC]

Yes, something like:

=Sumproduct(--($A$2:$A$10="Joe"),--($B$2:$B$10=10),$C$2:$C$10)

you can replace "Joe" and 10 with relative or partially relative reference cells containing those values so that you can copy down and/or across the formula.

see here for the Sumproduct() function and its many faces....

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Note: Pivot Tables may be another consideration as you will get all the unique combinations and their respective counts/sums.

[Piraneous]

most of what you said went over my head but the link was very usefull. got it working. thanks a lot