sumif functions based on multiple data

L

Lori

I have a worksheet where I'm trying to get the total sales in the south and
in the east. My formula looks for the words "south" and "east" in column A
and totals from column D. This is actually the example from microsoft help.
All the formula returns is #value using the following:

=SUM(IF((A2:A11="South")+(A2:A11="East"),D2:D11))

Region Salesperson Type Sales
South Buchanan Beverages 3571
West Davolio Dairy 3338
East Suyama Beverages 5122
North Suyama Dairy 6239
South Dodsworth Produce 8677
South Davolio Meat 450
South Davolio Meat 7673
East Suyama Produce 664
North Davolio Produce 1500
South Dodsworth Meat 6596
 
D

Dave Peterson

This is an array formula.

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

Alternatively:
=SUMPRODUCT((A2:A11={"South","East"})*D2:D11)

This does not have to be array entered.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top