SUMIF with INDEX and maybe MATCH, excel 2000

count von count

New Member
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

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

DonkeyOte

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

count von count

New Member
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

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

count von count

New Member
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

MrExcel MVP, Moderator
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

MrExcel MVP, Moderator
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.

Replies
1
Views
302
Replies
3
Views
332
Replies
1
Views
101
Replies
12
Views
390
Replies
2
Views
179

1,190,898
Messages
5,983,467
Members
439,843
Latest member
PlanetFitness

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back