Sum amounts in one column identified by type in another

Posted by Pedro on September 27, 2001 10:30 AM

A10:A200 are $ amounts. F10:F200 contains text which identifies the $amounts by one of several types (for instance a $50,000 amount in A10 and "credit" in F10 indicates a $50,000 credit sale). I would like a formula which will sum the dollar amount for each type, for instance in F1 I would like a formula to return the sum for "credit" sales.

Any help wil be appreciated.

Posted by Eric on September 27, 2001 10:40 AM

have you looked into pivot tables or the sumif command?

Pivot tables would be nice if you wanted to look at all of the categories in col F at once, sumif would work better for targeting specific categories in col F

to use the sumif formula, try entering the following in F1
=SUMIF(F10:F200,"credit",A10:A200)

also, you could change the part that says "credit" to another cell address, like E1, and then type the criteria you want to sum there. For instance type "credit" in E1 and get the sum of credits, type "cash" to get the sum of cash.

HTH

Posted by Barrie Davidson on September 27, 2001 10:40 AM

=SUMIF(F10:F200,"=credit",A10:A200)

Regards,
BarrieBarrie Davidson

Posted by Pedro on September 27, 2001 11:05 AM

Thanks Barrie & Eric, Slight Variation: can you help?

SUMIF works great. I realized in the process of implementing it that the "types" aren't mutually exclusive and I will also need a formula which totals amounts which fall into more than one type. For instance, the $50,000 in A10 might be identified as a "credit" sale in column F but also be identified as a "taxable" sale in column G. Could you offer a formula which would sum the amounts which satisfy both the "credit" and "taxable" criteria (ie, would exclude amounts which are not both).
Sorry I didn't think of the need for this earlier.
Pedro

Posted by Juan Pablo on September 27, 2001 11:32 AM

Re: Thanks Barrie & Eric, Slight Variation: can you help?

If you want to sum if F ="credit" and G="taxable" use this:

=SUMPRODUCT((F10:F200="credit")*(G10:G200="taxable")*A10:A200)

Juan Pablo

------------------

Posted by Pedro on September 27, 2001 11:56 AM

Excellent! Thank you, Juan Pablo (NT)