Using Getpivot in sumifs

cogswel__cogs

Board Regular
Joined
Jan 3, 2018
Messages
180
Hi,
I have daily dashboard that I created for work.
It uses pivottable for this months data, but for a number of reasons then uses sumifs to pull in budget and PY data based on date of month % of budget completed/booked at that period of month.

I am using conditional formatting to replicate the pivot table for example Grand total row is blueish anything below pivottable font is white so it wont show..

It all works very well aside from a few sales reps who might contribute to multiple divisions

When I am using the sumifs I am just adding the (divsional sum if +sales rep sum if), so regardless if the drilldown is open or not it will add the two sumifs (one having a value, and one not).

However there are a few sales rep who might contribute to two divisions, and my sumifs do not know that.
Was hoping to use get pivot or something in sumifs to indicate what division sumif should be using. Is this possible?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Tot attempt to further explain if I turn on get pivot and then click on the sales rep when the pivot able is dropdown is expanded excel creates the following formula.

=GETPIVOTDATA(" Charge Amount",$B$4,"Rep Number","5486","Div","TMT")

I am trying write a sumifs that knows "Div","TMT" for the cell. So I could use both the cells value(rep number) and it Div by in the sumifs

I could change to classic style, and force the reption of teh Div, but was wondering if there was a way to do without redesigning to classic style.
 
Upvote 0
The following function retrieves the last formula component. If this is not what you want, please explain.

Code:
Function gpd$(cell As Range)
Dim v
v = Split(cell.Formula, ",")
gpd = v(UBound(v))
gpd = Left(gpd, Len(gpd) - 1)
End Function

Plan5

I

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:153px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]21[/TD]
[TD="align: center"]43[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]22[/TD]
[TD="align: center"]"Y"[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
I21=GETPIVOTDATA("Amount",$H$8,"Region","Pacific","Closed","Y")
I22=gpd(I21)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
Conversely, you can get the pivot table cell’s address, if you know all the pieces to assemble the command:

Code:
Sub GetPTCellAddress()
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)
MsgBox pt.GetPivotData("Amount", "Region", "Pacific", "Closed", "Y").Address
End Sub
 
Upvote 0
Conversely, you can get the pivot table cell’s address, if you know all the pieces to assemble the command:

Code:
Sub GetPTCellAddress()
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)
MsgBox pt.GetPivotData("Amount", "Region", "Pacific", "Closed", "Y").Address
End Sub

This works incredibly cool and I am already using however it does exact opposite of what I was tryin to do.
Thie will tell you if you are looking Amount for Region = Pacific and "Closed Status" = Y it is in Cell c11

I want to know for C11 what is its region and Closed Status.
 
Last edited:
Upvote 0
If the pivot table layout is known, this kind of code should work:

Code:
Sub PTable()
Dim pt As PivotTable, desired As Range
Set desired = [j11]                                         ' pivot table cell
Set pt = ActiveSheet.PivotTables(1)
[h15] = Cells(pt.ColumnRange.Rows(2).Row, desired.Column)   ' region
[h16] = Cells(desired.Row, pt.DataLabelRange.Column)        ' status
End Sub


Plan5

HIJKL

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:102px;"><col style="width:128px;"><col style="width:47px;"><col style="width:39px;"><col style="width:76px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: center"]Sum of amount[/TD]
[TD="align: center"]Column labels[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: center"]row labels[/TD]
[TD="align: center"]Atlantic[/TD]
[TD="align: center"]Pacific[/TD]
[TD="align: center"]Polar[/TD]
[TD="align: center"]Grand total[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]86[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]231[/TD]
[TD="align: center"]322[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]43[/TD]

[TD="align: center"]75[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD="align: center"]Grand total[/TD]
[TD="align: center"]118[/TD]
[TD="align: center"]48[/TD]
[TD="align: center"]231[/TD]
[TD="align: center"]397[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]
[TD="align: center"]Pacific[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]
[TD="align: center"]Y[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top