Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif functions based on multiple data
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif functions based on multiple data
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif functions based on multiple data
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you create COUNTIF functions based on multiple criteria? | Excel Worksheet Functions | |||
SUMIF based on data in adjacent row | Excel Discussion (Misc queries) | |||
multiple selections in a sumif functions | Excel Worksheet Functions | |||
SUMIF in multiple columns based on other criteria in Excel? | Excel Discussion (Misc queries) | |||
SUMIF and MULTIPLE DATA | Excel Discussion (Misc queries) |