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.

Thanks in advance!

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

Maybe you need Sumif?

E.g

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

copied down.

=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

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

Try:

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

The first formula I gave does!

the second was an alternative!

...and mine doesn't need CSE confirmation

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.