# 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

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.

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

4. ## Re: Sumproduct - multiple conditions

I guess I am not having a good day understanding the problems

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.

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)))

7. ## Re: Sumproduct - multiple conditions

The first formula I gave does!

the second was an alternative!

8. ## Re: Sumproduct - multiple conditions

...and mine doesn't need CSE confirmation

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.

?

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.

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1