Sum values based on multiple criteria

[Jorrg1]

Hi

I would really appriciate if I could get some help with this dilemma.

In the yellow cell I want a formula that can sum up all the numbers in row D, based on which account numbers I choose in the box in row J. They have to be linked to the account number in row C.
So since I already have choosen the numbers 4000 and 4050, the amount in the yellow cell should be 47275.

I also have to be able to change the numbers in the box to get new results as I please. I need to be able to choose account numbers and automatically get new answers in the yellow cell.

Thank you!

Dilemma.xlsx

[Admin]

Hi Jorrg1,

Welcome to Excelfox!!

try

=SUMPRODUCT(SUMIF(C5:C15,J20:J27,D5:D15))

or

=SUM(INDEX(SUMIF(C5:C15,J20:J27,D5:D15),0,0))

[Jorrg1]

ah, thank you very much!!

[Jorrg1]

Is it also possible to link the names from the box in row J to M to formula? I have already locked some of them with 4F.

=SUMMERPRODUKT(SUMMERHVIS($C$5:$C$15;$J$20:$J$27;D 5:D15))

Please see attached file

Dilemma 2.xlsx

[Kevin@Radstock]

Hi

Or another version:
=SUMIF(C5:C15,J20,D5:D15)+SUMIF(C5:C15,J21,D5:D15)

Kevin

[Admin]

Hi

in D19 and copied down & across,

=SUMPRODUCT(SUMIF($C$5:$C$15,INDEX($J$20:$M$27,,MA TCH($C19,$J$19:$M$19,0)),D$5:D$15))

Note: Some of the descriptions have trailing spaces. So please remove it to get the correct results.

[Jorrg1]

Thank you very much. I tried to translate it from english to norwegian but I didnt get the right result. Is there something wrong with my formula? I pasted the formula in D19..

=SUMMERPRODUKT(SUMMERHVIS($C$5:$C$15;INDEKS($J$20: $M$27;SAMMENLIGNE($C19;$J$19:$M$19;0));D$5:D$15))

Transelation, from english to norwegian:

Sumproduct = summerprodukt, sumif = summerhvis, index = indeks, match = sammenligne,

We also use ";" instead of ","

Dilemma 3.xlsx

[Jorrg1]

I managed My formula had some trailing spaces

thank you!

[Admin]

Thanks for the feedback. I had already mentioned the trailing space in my previous post