Sum with multiple sum range

[xlstime]

Hi Masters,

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

sample sheet attached for your reference

 

[Luke M]

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)

 

[NARAYANK991]

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

 

[bosco_yip]

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

 

[xlstime]

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)

 

[xlstime]

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

 

[NARAYANK991]

Hi ,

So try this :

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

where F9:F12 contains the items to be matched.

Narayan

 

[Luke M]

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