Sumif with vertical range changing reference

[David Lam]

Hi Everyone,

I am stumped on this I have the logic but can't find a way to do it.

Attached is an spreadsheet with pivot table that will feed a sumifs formula. The issue is when a new "name" is introduced the sumifs reference will shift. I am trying to match my sumifs criteria than recalculate.

Ok just to give a clear example.

A user can change the start and end inquiry, this will sum anything within that range related to the fixed names. Say in an event where a new name is introduced say "#a". The pivot table will refresh and now #a is the new range for 10:10. Previously it was set up for just "a". Now my inquiry table for "a" is adding 10:10 which has "#a" values.

Help! I tried attempting to do this for 4 hours.

 

[Hui]

M24: =SUMIFS($A10:$F10,$A$9:$F$9,"<="&$M$21, $A$9:$F$9, ">=" & $M$20)
copy down

or
M24: =SUMPRODUCT(($B$10:$E$13)*($A$10:$A$13=$L24)*($B$9:$E$9>=$M$20)*($B$9:$E$9<=$M$21))
copy down

 

[David Lam]

Hi Hui, I really sorry I should of worded it more accurately.

1) Raw data is pasted into "raw data" tab
2) pivot table will than refresh in the "pivot table" tab
3) staff will inquire on the front end via the "staff inquiry" tab
Note: staff inquiry tab will have fix names

If I introduce anything new into the raw data tab and the pivot table will shift the order. Now the front end is hardcoded to fixed ranges so it's incorrectly adding the wrong reference. "a" is really 90.

 

[David Lam]

M24: =SUMIFS($A10:$F10,$A$9:$F$9,"<="&$M$21, $A$9:$F$9, ">=" & $M$20)
copy down

or
M24: =SUMPRODUCT(($B$10:$E$13)*($A$10:$A$13=$L24)*($B$9:$E$9>=$M$20)*($B$9:$E$9<=$M$21))
copy down

Wow I used this one it works with new items being listed on the pivot table!
=SUMPRODUCT(($B$10:$E$13)*($A$10:$A$13=$L24)*($B$9:$E$9>=$M$20)*($B$9:$E$9<=$M$21))

I will study this formula. I really appreciate this.

 

[David Lam]

Finished product! If anyone comes across this on the internet.

 

[bosco_yip]

Or.....

As per your post #.1 file,

M24, copied down :

=SUMIFS(INDEX($10:$13,MATCH(L24,A$10:A$13,0),0),$9:$9,"<="&$M$21,$9:$9,">="&$M$20)

Regards
Bosco

 

[bosco_yip]

Or.....

As per your post #.1 file,

M24, copied down :

=SUMIFS(INDEX($10:$13,MATCH(L24,A$10:A$13,0),0),$9:$9,"<="&$M$21,$9:$9,">="&$M$20)

Regards
Bosco

Another option,

=SUM(OFFSET(A$9,MATCH(L24,A$10:A$13,0),MATCH(M$20,B$9:E$9,0),1,M$21-M$20+1))

Regards
Bosco