J
Juan Sanchez
Erik
Just follow the logic... keep adding the criterias you
want to meet...
Lets say you want to sum A1:A10 x B1:B10 wich are both
numeric values but you want to sum only those which comply
with your criterion on C1:C10, D110,E1:E10... and so
on...
When using sumproduct the sintax is:
=SUMPRODUCT(range1,range2,range3,...,range_n) and this
will multiply each range's cell with the corresponding on
the other ranges and finally sum the total.
If one of the ranges is substituted with a formula like
=sumproduct(a1:a10,b1:b10,--(c1:c10="NY")) the third range
in this formula will return TRUE or FALSE and the double
minus (--) (its called UNARY, I think) will convert to 1
and 0 thus eliminating anithing that does not complys.
So you can use something like:
=SUMPRODUCT (RECEIVED,--(P&D!A1:A10="ny"),--(S&H!
C1:C10="Raw"),--(SHEET3!D110="APP"),P&D!B1:B10)
This will multiply and then sum Received x P&D!B1:B10 of
all records that are "NY" and "APP" and you can add along
some other criterion to meet.
Hope that helped...
Cheers
Juan
$AK$7:$AK$5000=4),'Received, Processing & Delivery'!
$F$7:$F$5000)
the criteria matches. I would like to add column 'B'
and 'C' into the formula but I don't know how. I need
the formula to do the following:
Just follow the logic... keep adding the criterias you
want to meet...
Lets say you want to sum A1:A10 x B1:B10 wich are both
numeric values but you want to sum only those which comply
with your criterion on C1:C10, D110,E1:E10... and so
on...
When using sumproduct the sintax is:
=SUMPRODUCT(range1,range2,range3,...,range_n) and this
will multiply each range's cell with the corresponding on
the other ranges and finally sum the total.
If one of the ranges is substituted with a formula like
=sumproduct(a1:a10,b1:b10,--(c1:c10="NY")) the third range
in this formula will return TRUE or FALSE and the double
minus (--) (its called UNARY, I think) will convert to 1
and 0 thus eliminating anithing that does not complys.
So you can use something like:
=SUMPRODUCT (RECEIVED,--(P&D!A1:A10="ny"),--(S&H!
C1:C10="Raw"),--(SHEET3!D110="APP"),P&D!B1:B10)
This will multiply and then sum Received x P&D!B1:B10 of
all records that are "NY" and "APP" and you can add along
some other criterion to meet.
Hope that helped...
Cheers
Juan
add a couple more arguments to it.-----Original Message-----
Hi,
I currently have a this formula in a cell but I need to
$AO$7:$AO$5000="masci")*('Received, Processing & Delivery'!=SUMPRODUCT(('Received, Processing & Delivery'!
$AK$7:$AK$5000=4),'Received, Processing & Delivery'!
$F$7:$F$5000)
current formula is set up, it will only sum clumn 'A' ifThis adds all the amounts in the cells matching my criteria.
I need to add two more columns into the argument as follows:
A B C AO AK
1 # masci 4
2 # # masci 4
3 # # masci 4
Column 'A' will always have a value and the way my
the criteria matches. I would like to add column 'B'
and 'C' into the formula but I don't know how. I need
the formula to do the following:
B2 and C3 if the criteria matches my sumproduct formula.If C1:C3>0 sum C1:C3 if not than if B1:B3>0, sum B1:B3 and so on for column 'A'
In my example above I would like the forumula to add A1,