Sumproduct - multiple conditions

**NBVC** · 06-04-2009, 03:32 PM

Try:

=SUMPRODUCT((ISNUMBER(MATCH('external sheet'!$A$1:$A$4613,$A$3:$A$8,0))*('external sheet'!$D$1:$D$4613)))

**rasonline** · 06-04-2009, 04:59 PM

Originally Posted by NBVC

Try:

=SUMPRODUCT((ISNUMBER(MATCH('external sheet'!$A$1:$A$4613,$A$3:$A$8,0))*('external sheet'!$D$1:$D$4613)))

OOOOOOOOKKKK!! Now we're talking.

Took me an hour to figure out the logic behind this but i do understand it now. Correct me if i'm wrong on this -
MATCH - this matches the complete list of accounts to those that i specify, and returns a number. If no match, then returns an N/A
ISNUMBER - where a number is returned, it will generate TRUE = 1

So this gives the column of accuont numbers a list of 0's and 1's. 1's correspond the account numbers i specify.
This is then multiplied out against the column with the values.

?

**NBVC** · 06-04-2009, 05:02 PM

Originally Posted by rasonline

OOOOOOOOKKKK!! Now we're talking.

Took me an hour to figure out the logic behind this but i do understand it now. Correct me if i'm wrong on this -
MATCH - this matches the complete list of accounts to those that i specify, and returns a number. If no match, then returns an N/A
ISNUMBER - where a number is returned, it will generate TRUE = 1

So this gives the column of accuont numbers a list of 0's and 1's. 1's correspond the account numbers i specify.
This is then multiplied out against the column with the values.

?

Precisely!

...and those multiplied values are summed up.. to give final total.