Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF with multiple criteria
I'm new to excel...but I know SUMIF checks an array for a particular
value and adds up the cells in a corresponding array for matches. Say I have three columns A=Salesperson, B=Item Sold, C=Amount. I want to sum up the total amount that salesman i sold of item j. I know how to find the total for all things a particular salesman sold, and i can find the total for all sales of a particular item, but how do i find a total of all sales by a salesman of a particular item? I know there are ways to implement this with filters and tons of other ways, but with the way the data is available to me, some kind of modified sumif strategy would be best |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF with multiple criteria
Try this:
A1:A10 = names B1:B10 = item sold C1:C10 = amount =SUMPRODUCT(--(A1:A10="name"),--(B1:B10="item"),C1:C10) Better to use cells to hold the criteria: E1 = Joe F1 = book =SUMPRODUCT(--(A1:A10=E1),--(B1:B10=F1),C1:C10) -- Biff Microsoft Excel MVP "ricky" wrote in message ups.com... I'm new to excel...but I know SUMIF checks an array for a particular value and adds up the cells in a corresponding array for matches. Say I have three columns A=Salesperson, B=Item Sold, C=Amount. I want to sum up the total amount that salesman i sold of item j. I know how to find the total for all things a particular salesman sold, and i can find the total for all sales of a particular item, but how do i find a total of all sales by a salesman of a particular item? I know there are ways to implement this with filters and tons of other ways, but with the way the data is available to me, some kind of modified sumif strategy would be best |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF with multiple criteria
Try something like this:
=sumproduct(--(A1:A100="Salesperson"), --(B1:B100="Item Sold"), C1:C100) but you can't use an entire column (A:A) unless you have XL2007. More on multiple condition tests he http://xldynamic.com/source/xld.SUMPRODUCT.html "ricky" wrote: I'm new to excel...but I know SUMIF checks an array for a particular value and adds up the cells in a corresponding array for matches. Say I have three columns A=Salesperson, B=Item Sold, C=Amount. I want to sum up the total amount that salesman i sold of item j. I know how to find the total for all things a particular salesman sold, and i can find the total for all sales of a particular item, but how do i find a total of all sales by a salesman of a particular item? I know there are ways to implement this with filters and tons of other ways, but with the way the data is available to me, some kind of modified sumif strategy would be best |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF with multiple criteria
Thanks so much. I had tried sumproduct but was comparing the whole
columns as I don't know how many records there might be in the future...I guess that was my problem. Got it now...stupid Office 03 guess I should upgrade On Jul 7, 2:38 pm, JMB wrote: Try something like this: =sumproduct(--(A1:A100="Salesperson"), --(B1:B100="Item Sold"), C1:C100) but you can't use an entire column (A:A) unless you have XL2007. More on multiple condition tests he http://xldynamic.com/source/xld.SUMPRODUCT.html "ricky" wrote: I'm new to excel...but I know SUMIF checks an array for a particular value and adds up the cells in a corresponding array for matches. Say I have three columns A=Salesperson, B=Item Sold, C=Amount. I want to sum up the total amount that salesman i sold of item j. I know how to find the total for all things a particular salesman sold, and i can find the total for all sales of a particular item, but how do i find a total of all sales by a salesman of a particular item? I know there are ways to implement this with filters and tons of other ways, but with the way the data is available to me, some kind of modified sumif strategy would be best |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF with multiple criteria
You could use A1:A65535
"ricky" wrote: Thanks so much. I had tried sumproduct but was comparing the whole columns as I don't know how many records there might be in the future...I guess that was my problem. Got it now...stupid Office 03 guess I should upgrade On Jul 7, 2:38 pm, JMB wrote: Try something like this: =sumproduct(--(A1:A100="Salesperson"), --(B1:B100="Item Sold"), C1:C100) but you can't use an entire column (A:A) unless you have XL2007. More on multiple condition tests he http://xldynamic.com/source/xld.SUMPRODUCT.html "ricky" wrote: I'm new to excel...but I know SUMIF checks an array for a particular value and adds up the cells in a corresponding array for matches. Say I have three columns A=Salesperson, B=Item Sold, C=Amount. I want to sum up the total amount that salesman i sold of item j. I know how to find the total for all things a particular salesman sold, and i can find the total for all sales of a particular item, but how do i find a total of all sales by a salesman of a particular item? I know there are ways to implement this with filters and tons of other ways, but with the way the data is available to me, some kind of modified sumif strategy would be best |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif with multiple criteria | Excel Discussion (Misc queries) | |||
Sumif with multiple criteria | Excel Discussion (Misc queries) | |||
SUMIF for Multiple Criteria | Excel Worksheet Functions | |||
SUMIF with multiple criteria | Excel Worksheet Functions | |||
SUMIF, multiple criteria | Excel Discussion (Misc queries) |