Dynamic Formula Approach to Pivot Table (Don't have access to pivotby)

M

MountainFog

Board Regular
Joined
Nov 16, 2023
Messages
140
Office Version
  1. 365
Platform
  1. Windows
Apologies for the screenshots. I cannot use the tool due to work requirements.

I have a Dynamic Table as below. I'm looking for a formula approach to making a summary table as shown in the screenshots. I was planning on using sum/filter but I would prefer an approach that is completely dynamic that when a new line is added to the table, new columns/ data flows into summary table.

I know that this would most easily be done in PQ or Pivot Tables, but I want something that doesn't have to be refreshed.

VendorInvoice #Month EndingInvoice TypeInv AmountCost to Client
Walmart1846132618
9/30/2023​
Groceries$ 99,721.00$ 104,707.05
Walmart4634262989
10/31/2023​
Late Fee$ 3,973.00$ 3,973.00
Walmart9422785653
10/31/2023​
Electronics$ 73,491.00$ 77,165.55
Walmart9879281222
10/31/2023​
Sports$ 98,344.00$ 103,261.20
Walmart8879847125
10/31/2023​
Groceries$ 40,394.00$ 42,413.70
Walmart8684814945
11/30/2023​
Electronics$ 10,118.00$ 10,623.90
Walmart7383414451
11/30/2023​
Sports$ 56,898.00$ 59,742.90
Walmart7147381965
11/30/2023​
Groceries$ 32,783.00$ 34,422.15
Walmart5954753819
12/31/2023​
Sports$ 26,222.00$ 27,533.10
Walmart6841524331
12/31/2023​
Electronics$ 20,760.00$ 21,798.00
Walmart2722824191
12/31/2023​
Groceries$ 81,251.00$ 85,313.55
Walmart4789856372
1/31/2024​
Electronics$ 39,912.00$ 41,907.60
Walmart3418285762
1/31/2024​
Sports$ 50,764.00$ 53,302.20
Walmart9916273979
1/31/2024​
Groceries$ 57,117.00$ 59,972.85
Walmart9888987823
2/29/2024​
Sports$ 53,233.00$ 55,894.65
Walmart2216292814
2/29/2024​
Electronics$ 32,953.00$ 34,600.65
Walmart6173467393
2/29/2024​
Groceries$ 19,606.00$ 20,586.30
Walmart4738125262
3/31/2024​
Electronics$ 13,336.00$ 14,002.80
Walmart5875491832
3/31/2024​
Sports$ 36,549.00$ 38,376.45
Walmart3594253512
3/31/2024​
Groceries$ 22,754.00$ 23,891.70

1712588986199.png
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
How about
Fluff.xlsm
ABCDEFGHIJKLMNOP
1VendorInvoice #Month EndingInvoice TypeInv AmountCost to Client30/09/202331/10/202330/11/202331/12/202331/01/202429/02/202431/03/2024
2Walmart184613261830/09/2023Groceries99,721.00104,707.05WalmartElectronics077165.5510623.92179841907.634600.6514002.8
3Walmart463426298931/10/2023Late Fee3,973.003,973.00WalmartGroceries104707.0542413.734422.1585313.5559972.8520586.323891.7
4Walmart942278565331/10/2023Electronics73,491.0077,165.55WalmartLate Fee0397300000
5Walmart987928122231/10/2023Sports98,344.00103,261.20WalmartSports0103261.259742.927533.153302.255894.6538376.45
6Walmart887984712531/10/2023Groceries40,394.0042,413.70
7Walmart868481494530/11/2023Electronics10,118.0010,623.90
8Walmart738341445130/11/2023Sports56,898.0059,742.90
9Walmart714738196530/11/2023Groceries32,783.0034,422.15
10Walmart595475381931/12/2023Sports26,222.0027,533.10
11Walmart684152433131/12/2023Electronics20,760.0021,798.00
12Walmart272282419131/12/2023Groceries81,251.0085,313.55
13Walmart478985637231/01/2024Electronics39,912.0041,907.60
14Walmart341828576231/01/2024Sports50,764.0053,302.20
15Walmart991627397931/01/2024Groceries57,117.0059,972.85
16Walmart988898782329/02/2024Sports53,233.0055,894.65
17Walmart221629281429/02/2024Electronics32,953.0034,600.65
18Walmart617346739329/02/2024Groceries19,606.0020,586.30
19Walmart473812526231/03/2024Electronics13,336.0014,002.80
20Walmart587549183231/03/2024Sports36,549.0038,376.45
21Walmart359425351231/03/2024Groceries22,754.0023,891.70
22
Sheet6
Cell Formulas
RangeFormula
H2:I5H2=SORT(UNIQUE(CHOOSECOLS(FILTER(A2:D100,A2:A100<>""),1,4)),2)
J1:P1J1=TOROW(UNIQUE(C2:C100),1)
J2:P5J2=SUMIFS(F:F,A:A,INDEX(H2#,,1),D:D,INDEX(H2#,,2),C:C,J1#)
Dynamic array formulas.
 
Upvote 0
Solution
Thank you! That's what I ending up doing. Maybe you can shed some light on this. I was under the impression that any of the IFS (SUMIFS, COUNTIFS, etc.) could not take an array as a criteria?
 
Upvote 0
They can take an array as the criteria, but not as the Criteria Range.
 
Upvote 1
This version handles multiple vendors, and creates totals. I'm not sure if you need that, but I worked it out, so here it is.

david763.xlsx
ABCDEFGHIJKLMNOPQ
1VendorInvoice #Month EndingInvoice Type Inv Amount Cost to Client Sum of Cost to Client:Month Ending
2Walmart18461326189/30/2023Groceries$ 99,721.00$ 104,707.05VendorInvoice Type9/30/202310/31/202311/30/202312/31/20231/31/20242/29/20243/31/2024
3Walmart463426298910/31/2023Late Fee$ 3,973.00$ 3,973.00K*MartGroceries$ -$ 15.00$ -$ -$ -$ -$ -
4Walmart942278565310/31/2023Electronics$ 73,491.00$ 77,165.55K*MartSports$ -$ 9.00$ -$ -$ -$ 10.00$ -
5Walmart987928122210/31/2023Sports$ 98,344.00$ 103,261.20K*MartElectronics$ -$ -$ -$ -$ -$ 23.00$ -
6Walmart887984712510/31/2023Groceries$ 40,394.00$ 42,413.70K*Mart Total$ -$ 24.00$ -$ -$ -$ 33.00$ -
7Walmart868481494511/30/2023Electronics$ 10,118.00$ 10,623.90WalmartGroceries$ 104,707.05$ 42,413.70$ 34,422.15$ 85,313.55$ 59,972.85$ 20,586.30$ 23,891.70
8Walmart738341445111/30/2023Sports$ 56,898.00$ 59,742.90WalmartLate Fee$ -$ 3,973.00$ -$ -$ -$ -$ -
9Walmart714738196511/30/2023Groceries$ 32,783.00$ 34,422.15WalmartElectronics$ -$ 77,165.55$ 10,623.90$ 21,798.00$ 41,907.60$ 34,600.65$ 14,002.80
10Walmart595475381912/31/2023Sports$ 26,222.00$ 27,533.10WalmartSports$ -$ 103,261.20$ 59,742.90$ 27,533.10$ 53,302.20$ 55,894.65$ 38,376.45
11Walmart684152433112/31/2023Electronics$ 20,760.00$ 21,798.00Walmart Total$ 104,707.05$ 226,813.45$ 104,788.95$ 134,644.65$ 155,182.65$ 111,081.60$ 76,270.95
12Walmart272282419112/31/2023Groceries$ 81,251.00$ 85,313.55Grand Totals$ 104,707.05$ 226,837.45$ 104,788.95$ 134,644.65$ 155,182.65$ 111,114.60$ 76,270.95
13Walmart47898563721/31/2024Electronics$ 39,912.00$ 41,907.60
14Walmart34182857621/31/2024Sports$ 50,764.00$ 53,302.20
15Walmart99162739791/31/2024Groceries$ 57,117.00$ 59,972.85
16Walmart98889878232/29/2024Sports$ 53,233.00$ 55,894.65
17Walmart22162928142/29/2024Electronics$ 32,953.00$ 34,600.65
18Walmart61734673932/29/2024Groceries$ 19,606.00$ 20,586.30
19Walmart47381252623/31/2024Electronics$ 13,336.00$ 14,002.80
20Walmart58754918323/31/2024Sports$ 36,549.00$ 38,376.45
21Walmart35942535123/31/2024Groceries$ 22,754.00$ 23,891.70
22K*Mart110/31/2023Groceries$ 1.00$ 7.00
23K*Mart210/31/2023Groceries$ 2.00$ 8.00
24K*Mart310/31/2023Sports$ 3.00$ 9.00
25K*Mart42/29/2024Sports$ 4.00$ 10.00
26K*Mart52/29/2024Electronics$ 5.00$ 11.00
27K*Mart62/29/2024Electronics$ 6.00$ 12.00
28
29
30
Sheet9
Cell Formulas
RangeFormula
I3:J12I3=LET(rnga,A2:A30,rngd,D2:D30,c,COUNTA(rnga),s,SEQUENCE(2*c+1),t,IF(s<=c,CHOOSE({1,2},rnga,rngd),IF(s<=2*c,CHOOSE({1,2},INDEX(rnga,s-c,0)&" Total",""),{"zzz",""})),su,SORT(UNIQUE(t)),IF(su="zzz","Grand Totals",su))
K2:Q2K2=TRANSPOSE(SORT(UNIQUE((FILTER(C2:C30,C2:C30<>"")))))
K3:Q12K3=LET(v,A2:A30,me,C2:C30,it,D2:D30,cc,F2:F30,a,INDEX(I3#,0,1),IF(a="Grand Totals",SUMIFS(cc,me,K2#),IF(RIGHT(a,5)="Total",SUMIFS(cc,me,K2#,v,LEFT(a,LEN(a)-6)),SUMIFS(cc,v,a,it,INDEX($I$3#,0,2),me,$K$2#))))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I:ICell Valuecontains "Total"textNO
I1:U2Expression=(I$2<>"")textNO
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

Share this page

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