SUMIF with INDEX and maybe MATCH, excel 2000

[count von count]

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

 

[DonkeyOte]

any reason why you can't use the GETPIVOTDATA function to retrieve the appropriate value ?

 

[count von count]

Not used that function before, like it, thanks for the tip.

The only limitation is that it doesn't allow wildcards, e.g. getpivotdata(a4,"IT*") returns #N/A when I'm trying to sum:
IT
IT Banking
IT Europe
IT Public Sector

 

[Jon von der Heyden]

Is there any particular reason that you cannot use whole columns in your SUMIF()?

 

[count von count]

Yes, because I only want to sum a subset of the data in the table. Unable to upload a picture right now but if my pivot table has two row variables, say geography and market, I want to sum all the UK IT markets, where IT could be
IT
IT Banking
IT Commercial
IT Public Sector

So the sumif range needs to know where the UK part of the table starts and ends, which is indicated by the position of "UK" and "UK total" in col. A, the Geography column.

Is there any particular reason that you cannot use whole columns in your SUMIF()?

 

[Jon von der Heyden]

I think you should possibly be running the formula on the pivot table source data. If possible supply us with a sample of the source data. I'm thinking you need some form of a SUMPRODUCT...

 

[RoryA]

You could group the IT fields within the pivot table, and then you can use GETPIVOTDATA to return the total for the group for a given area.