How to sum values based on multiple array values?

[mortenhn]

Hi guys

I hope you can help me make a nice solution for this one.

In the enclosed spreadsheet I am trying to sum the total sum range of column B where the value in column A is equal to a value in column C.

I can express this in a SUMIF function as such: =SUMIF(A:A;C:C;B:B). The only problem with this is that I have to drill down with the formula so that it checks each criteria cell. I would like for it to sum it up in total in cell I have picked for the formula (f2 in my spreadsheet).

Maybe sumproduct could be of help but I have not been able to make it work...

Do any of you guys know of an easy solution?

Thanks in advance
Kind regards
Morten

[Glenn Kennedy]

Hi. Try this:

=SUMPRODUCT($B$2:$B$21*COUNTIF($C$2:$C$3,$A$2:$A$21))

You may need ; instead of ,

[KOKOSEK]

Into F2:

Please Login or Register  to view this content.

array formulas, need CSE

[Bo_Ry]

Another one

=SUMPRODUCT(SUMIFS(B2:B21,A2:A21,C2:C3))

[mortenhn]

Great @ Bo_Ry and Koksek - both works perfect! Thanks!

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[mortenhn]

Hi. Try this:

=SUMPRODUCT($B$2:$B$21*COUNTIF($C$2:$C$3,$A$2:$A$21))

You may need ; instead of ,

Thanks for the suggestion, but sadly this does not work for me as it multiplies the result according to the number of cells populated. Thanks anyways though