sum based on specific criteria

[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

-----Original Message-----
Hi,

I currently have a this formula in a cell but I need to

add a couple more arguments to it.

=SUMPRODUCT(('Received, Processing & Delivery'!

$AO$7:$AO$5000="masci")*('Received, Processing & Delivery'!
$AK$7:$AK$5000=4),'Received, Processing & Delivery'!
$F$7:$F$5000)

This 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

current formula is set up, it will only sum clumn 'A' if
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:

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,

B2 and C3 if the criteria matches my sumproduct formula.

 

[Juan Sanchez]

Erik

I forgot to say in the other post that, and you probably
allready know, all ranges in sumproduct *must* be the same
size, since you are using a named range you wan't to make
sure that the named range has the same size as the
referenced ranges. if not, you'll get #VALUE!

Cheers
Juan