# Sumproduct - multiple conditions

1. ## Sumproduct - multiple conditions

Hi

i've tried searching a bit but i'm still new with SUMPRODUCT and not sure how to find a solution to this. Attached is a workbook showing what i'm trying to achieve.

I am currently using VLOOKUP to pull info from an EXTERNAL workbook. 5 account numbers are listed on separate lines, one vlookup for each line, and the result is summed to give a total.

At this stage, I am only able to write the sumproduct on individual lines to achieve the same effect as a sumif. I could combine these formulas into one long sumproduct but that would be quite tedious.

I want to know if there is a simple SUMPRODUCT formula that can be written where i simply specify the full list of accounts, data to be summed, and then highlight all required accounts at once.

i am reluctant to write out one very long sumproduct formula specifying each account number individually. My main aim is to specify the account numbers in combination (see red highlight in the attachment), and to have the answer in one cell.

In the workbook
External sheet - column A account number, column D is the value i want to pull
Lookup - column A account number  Register To Reply

2. ## Re: Sumproduct - multiple conditions

Maybe you need Sumif?

E.g

=SUMIF('external sheet'!\$A\$1:\$A\$4613,A3,'external sheet'!\$D\$1:\$D\$4613)

copied down.  Register To Reply

3. ## Re: Sumproduct - multiple conditions

=SUMPRODUCT(('external sheet'!\$A\$1:\$A\$4613=TRANSPOSE(lookup!A3:A8))*('external sheet'!D1:D4613))
Entered with Ctrl+Shift+Enter

alternatively

=SUMPRODUCT(('external sheet'!\$A\$1:\$A\$4613={1400,1402,1403,1404,1405,1409})*('external sheet'!D1:D4613))

confirmed with just enter  Register To Reply

4. ## Re: Sumproduct - multiple conditions

I guess I am not having a good day understanding the problems   Register To Reply

5. ## Re: Sumproduct - multiple conditions Originally Posted by squiggler47 =SUMPRODUCT(('external sheet'!\$A\$1:\$A\$4613={1400,1402,1403,1404,1405,1409})*('external sheet'!D1:D4613))

confirmed with just enter
This gives the answer i'm looking for, and in one cell. The only problem is that i'm trying to avoid having to type in the account numbers in the formula.

In this solution we had to type the account numbers 1400,1402,1403,1404,1405,1409

Is there no way to specify the account numbers as a range of cells?
eg. something along the lines of:
=SUMPRODUCT(('external sheet'!\$A\$1:\$A\$4613={a3:a8})*('external sheet'!D1:D4613))

I am trying to write a standard sumproduct formula which i can then copy and paste to other sheets, which will then allow me the flexibility to change the account numbers without having to go into the sumproduct formula.  Register To Reply

6. ## Re: Sumproduct - multiple conditions

Try:

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

7. ## Re: Sumproduct - multiple conditions

The first formula I gave does!

the second was an alternative!  Register To Reply

8. ## Re: Sumproduct - multiple conditions

...and mine doesn't need CSE confirmation   Register To Reply

9. ## Re: Sumproduct - multiple conditions 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.

?  Register To Reply

10. ## Re: Sumproduct - multiple conditions 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.  Register To Reply

11. ## Re: Sumproduct - multiple conditions Originally Posted by squiggler47 The first formula I gave does!

the second was an alternative!
thanks for that. i just find that when working with array formulas that need ctrl+shift+enter that it becomes a little less flexible. but yes, your formula does indeed work.

thanks for the help.  Register To Reply