SumIf + And

[Idolminder]

I want to draw out information from a table of fields if two conditions are
true. SumIF doesn't seem to handle it, but I may be using it incorrectly.

A1=products, B1=price, C1=quantity sold, D1=quarter sold in

A2=carrots, $1.00, 20, 4
A3=beets, $2.00, 10, 4
A4=carrots, $1.00, 15, 3
A5=carrots, $1.00, 10, 4

So what I'm trying to get out of this is a sum of the Quantity of carrots
sold during each quarter. To me that's the Sum of quantities, If quarter = 1
and product = carrots - but I'm not sure how to build it...

Thoughts?

 

[John C]

Easier way, use SUMPRODUCT.

=SUMPRODUCT(--($A$2:$A$5=vegetable),--($D$2:$D$5=quarter),($C$2:$C$5))
where vegetable is a reference to cell that shows what vegetable you are
searching for, and quarter is which quarter you are calculating. Obviously,
expand the ranges beyond row 5 as far as needed. Ensure all ranges are the
exact same length.

 

[David Biddulph]

=SUMPRODUCT((A2:A5="carrots")*(D25=1)*(C2:C5)) if you want the quantity.
=SUMPRODUCT((A2:A5="carrots")*(D25=1)*(C2:C5)*(B2:B5)) if you wish to
multiply by the price too.