• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sum with multiple sum range

xlstime

Active Member
Hi Masters,

Could you please help to sum range with multiple sum range.

sample sheet attached for your reference
 

Attachments

  • sample sheet.xlsx
    9.4 KB · Views: 13
If criteria can be from either field, can you just cut/paste them to be below each other? Otherwise, have to call all possibilities

=SUMIF(E3:E5,F9,F3:F5)
+SUMIF(E3:E5,F10,F3:F5)
+SUMIF(G3:G5,F9,H3:H5)
+SUMIF(G3:G5,F10,H3:H5)
 
If criteria can be from either field, can you just cut/paste them to be below each other? Otherwise, have to call all possibilities
=SUMIF(E3:E5,F9,F3:F5)
+SUMIF(E3:E5,F10,F3:F5)
+SUMIF(G3:G5,F9,H3:H5)
+SUMIF(G3:G5,F10,H3:H5)

This formula can shortened to :

=SUMPRODUCT(SUMIF(E3:G5,F9:F10,F3:H5))

Regards
Bosco
 
Thanks Luke, but as i mentioned in the excel attached that the values can be from different filed (filed 1 or filed 2) and the list is complete dynamic in terms of records may be 2,3 or 4 records in that list in future .



If criteria can be from either field, can you just cut/paste them to be below each other? Otherwise, have to call all possibilities

=SUMIF(E3:E5,F9,F3:F5)
+SUMIF(E3:E5,F10,F3:F5)
+SUMIF(G3:G5,F9,H3:H5)
+SUMIF(G3:G5,F10,H3:H5)
 
Thanks NARAYANK991, but as i mentioned in the attachment that the list is completely dynamic and may be add more values in that list 2,3,5, or 10


Hi ,

Try this :

=SUM(SUMIF(OFFSET(E3:E5,,{0,2}),F9,OFFSET(E3:E5,,{1,3})) + SUMIF(OFFSET(E3:E5,,{0,2}),F10,OFFSET(E3:E5,,{1,3})))

Narayan
 
Thanks Luke, but as i mentioned in the excel attached that the values can be from different filed (filed 1 or filed 2) and the list is complete dynamic in terms of records may be 2,3 or 4 records in that list in future .
Yes...my formula allowed for criteria to be from either field. Did you check?
Also, if your ranges will be bigger, just start with bigger range in formula. I just used a small range because that's what was in your example. To handle a much larger area...

=SUMIF(E3:E5000,F9,F3:F5000)
+SUMIF(E3:E5000,F10,F3:F5000)
+SUMIF(G3:G5000,F9,H3:H5000)
+SUMIF(G3:G5000,F10,H3:H5000)

Boom. Now you can handle almost 5000 records.

Edit: Incorporating Bosco's idea:
=SUMPRODUCT(SUMIF(E3:G5000,F9:F10,F3:H5000))
 
Back
Top