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

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

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

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

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