count von count
New Member
- Joined
- Nov 9, 2007
- Messages
- 29
I'm trying to formulate a SUMIF that looks into a pivot table, problem is that if the pivot table changes on refresh, the SUMIF range may become wrong so I need to somehow combine SUMIF with INDEX, and possibly MATCH to ensure SUMIF always looks at the right range.
Simple form =SUMIF($B$25:$B$41,$J40,C$25:C$41). Column A of my pivot table has geographical location, so A25 says "UK" and A41 says "UK Total". So my sumif formula needs to recognise that if the pivot table changes on refresh, "UK" may now appear in A30 and "UK Total" in A50, so that sumif would become =SUMIF($B$30:$B$50,$J40,C$30:C$50).
Hope I've explained myself; thanks for any insights
Simple form =SUMIF($B$25:$B$41,$J40,C$25:C$41). Column A of my pivot table has geographical location, so A25 says "UK" and A41 says "UK Total". So my sumif formula needs to recognise that if the pivot table changes on refresh, "UK" may now appear in A30 and "UK Total" in A50, so that sumif would become =SUMIF($B$30:$B$50,$J40,C$30:C$50).
Hope I've explained myself; thanks for any insights