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

Try


=SUM(SUMIF(A2:A11,{"South","East"},D2:D11))



--
Regards,

Peo Sjoblom



"Lori" wrote in message
...
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

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.

Lori wrote:

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

--

Dave Peterson