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

[MountainFog]

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.

Vendor	Invoice #	Month Ending	Invoice Type	Inv Amount	Cost to Client
Walmart	1846132618	9/30/2023​	Groceries	$ 99,721.00	$ 104,707.05
Walmart	4634262989	10/31/2023​	Late Fee	$ 3,973.00	$ 3,973.00
Walmart	9422785653	10/31/2023​	Electronics	$ 73,491.00	$ 77,165.55
Walmart	9879281222	10/31/2023​	Sports	$ 98,344.00	$ 103,261.20
Walmart	8879847125	10/31/2023​	Groceries	$ 40,394.00	$ 42,413.70
Walmart	8684814945	11/30/2023​	Electronics	$ 10,118.00	$ 10,623.90
Walmart	7383414451	11/30/2023​	Sports	$ 56,898.00	$ 59,742.90
Walmart	7147381965	11/30/2023​	Groceries	$ 32,783.00	$ 34,422.15
Walmart	5954753819	12/31/2023​	Sports	$ 26,222.00	$ 27,533.10
Walmart	6841524331	12/31/2023​	Electronics	$ 20,760.00	$ 21,798.00
Walmart	2722824191	12/31/2023​	Groceries	$ 81,251.00	$ 85,313.55
Walmart	4789856372	1/31/2024​	Electronics	$ 39,912.00	$ 41,907.60
Walmart	3418285762	1/31/2024​	Sports	$ 50,764.00	$ 53,302.20
Walmart	9916273979	1/31/2024​	Groceries	$ 57,117.00	$ 59,972.85
Walmart	9888987823	2/29/2024​	Sports	$ 53,233.00	$ 55,894.65
Walmart	2216292814	2/29/2024​	Electronics	$ 32,953.00	$ 34,600.65
Walmart	6173467393	2/29/2024​	Groceries	$ 19,606.00	$ 20,586.30
Walmart	4738125262	3/31/2024​	Electronics	$ 13,336.00	$ 14,002.80
Walmart	5875491832	3/31/2024​	Sports	$ 36,549.00	$ 38,376.45
Walmart	3594253512	3/31/2024​	Groceries	$ 22,754.00	$ 23,891.70

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Vendor	Invoice #	Month Ending	Invoice Type	Inv Amount	Cost to Client				30/09/2023	31/10/2023	30/11/2023	31/12/2023	31/01/2024	29/02/2024	31/03/2024
2	Walmart	1846132618	30/09/2023	Groceries	99,721.00	104,707.05		Walmart	Electronics	0	77165.55	10623.9	21798	41907.6	34600.65	14002.8
3	Walmart	4634262989	31/10/2023	Late Fee	3,973.00	3,973.00		Walmart	Groceries	104707.05	42413.7	34422.15	85313.55	59972.85	20586.3	23891.7
4	Walmart	9422785653	31/10/2023	Electronics	73,491.00	77,165.55		Walmart	Late Fee	0	3973	0	0	0	0	0
5	Walmart	9879281222	31/10/2023	Sports	98,344.00	103,261.20		Walmart	Sports	0	103261.2	59742.9	27533.1	53302.2	55894.65	38376.45
6	Walmart	8879847125	31/10/2023	Groceries	40,394.00	42,413.70
7	Walmart	8684814945	30/11/2023	Electronics	10,118.00	10,623.90
8	Walmart	7383414451	30/11/2023	Sports	56,898.00	59,742.90
9	Walmart	7147381965	30/11/2023	Groceries	32,783.00	34,422.15
10	Walmart	5954753819	31/12/2023	Sports	26,222.00	27,533.10
11	Walmart	6841524331	31/12/2023	Electronics	20,760.00	21,798.00
12	Walmart	2722824191	31/12/2023	Groceries	81,251.00	85,313.55
13	Walmart	4789856372	31/01/2024	Electronics	39,912.00	41,907.60
14	Walmart	3418285762	31/01/2024	Sports	50,764.00	53,302.20
15	Walmart	9916273979	31/01/2024	Groceries	57,117.00	59,972.85
16	Walmart	9888987823	29/02/2024	Sports	53,233.00	55,894.65
17	Walmart	2216292814	29/02/2024	Electronics	32,953.00	34,600.65
18	Walmart	6173467393	29/02/2024	Groceries	19,606.00	20,586.30
19	Walmart	4738125262	31/03/2024	Electronics	13,336.00	14,002.80
20	Walmart	5875491832	31/03/2024	Sports	36,549.00	38,376.45
21	Walmart	3594253512	31/03/2024	Groceries	22,754.00	23,891.70
22
Sheet6

Cell Formulas
Range	Formula
H2:I5	H2	=SORT(UNIQUE(CHOOSECOLS(FILTER(A2:D100,A2:A100<>""),1,4)),2)
J1:P1	J1	=TOROW(UNIQUE(C2:C100),1)
J2:P5	J2	=SUMIFS(F:F,A:A,INDEX(H2#,,1),D:D,INDEX(H2#,,2),C:C,J1#)
Dynamic array formulas.

 

[MountainFog]

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?

 

[Fluff]

They can take an array as the criteria, but not as the Criteria Range.

 

[Eric W]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Vendor	Invoice #	Month Ending	Invoice Type	Inv Amount	Cost to Client			Sum of Cost to Client:		Month Ending
2	Walmart	1846132618	9/30/2023	Groceries	$ 99,721.00	$ 104,707.05			Vendor	Invoice Type	9/30/2023	10/31/2023	11/30/2023	12/31/2023	1/31/2024	2/29/2024	3/31/2024
3	Walmart	4634262989	10/31/2023	Late Fee	$ 3,973.00	$ 3,973.00			K*Mart	Groceries	$ -	$ 15.00	$ -	$ -	$ -	$ -	$ -
4	Walmart	9422785653	10/31/2023	Electronics	$ 73,491.00	$ 77,165.55			K*Mart	Sports	$ -	$ 9.00	$ -	$ -	$ -	$ 10.00	$ -
5	Walmart	9879281222	10/31/2023	Sports	$ 98,344.00	$ 103,261.20			K*Mart	Electronics	$ -	$ -	$ -	$ -	$ -	$ 23.00	$ -
6	Walmart	8879847125	10/31/2023	Groceries	$ 40,394.00	$ 42,413.70			K*Mart Total		$ -	$ 24.00	$ -	$ -	$ -	$ 33.00	$ -
7	Walmart	8684814945	11/30/2023	Electronics	$ 10,118.00	$ 10,623.90			Walmart	Groceries	$ 104,707.05	$ 42,413.70	$ 34,422.15	$ 85,313.55	$ 59,972.85	$ 20,586.30	$ 23,891.70
8	Walmart	7383414451	11/30/2023	Sports	$ 56,898.00	$ 59,742.90			Walmart	Late Fee	$ -	$ 3,973.00	$ -	$ -	$ -	$ -	$ -
9	Walmart	7147381965	11/30/2023	Groceries	$ 32,783.00	$ 34,422.15			Walmart	Electronics	$ -	$ 77,165.55	$ 10,623.90	$ 21,798.00	$ 41,907.60	$ 34,600.65	$ 14,002.80
10	Walmart	5954753819	12/31/2023	Sports	$ 26,222.00	$ 27,533.10			Walmart	Sports	$ -	$ 103,261.20	$ 59,742.90	$ 27,533.10	$ 53,302.20	$ 55,894.65	$ 38,376.45
11	Walmart	6841524331	12/31/2023	Electronics	$ 20,760.00	$ 21,798.00			Walmart Total		$ 104,707.05	$ 226,813.45	$ 104,788.95	$ 134,644.65	$ 155,182.65	$ 111,081.60	$ 76,270.95
12	Walmart	2722824191	12/31/2023	Groceries	$ 81,251.00	$ 85,313.55			Grand Totals		$ 104,707.05	$ 226,837.45	$ 104,788.95	$ 134,644.65	$ 155,182.65	$ 111,114.60	$ 76,270.95
13	Walmart	4789856372	1/31/2024	Electronics	$ 39,912.00	$ 41,907.60
14	Walmart	3418285762	1/31/2024	Sports	$ 50,764.00	$ 53,302.20
15	Walmart	9916273979	1/31/2024	Groceries	$ 57,117.00	$ 59,972.85
16	Walmart	9888987823	2/29/2024	Sports	$ 53,233.00	$ 55,894.65
17	Walmart	2216292814	2/29/2024	Electronics	$ 32,953.00	$ 34,600.65
18	Walmart	6173467393	2/29/2024	Groceries	$ 19,606.00	$ 20,586.30
19	Walmart	4738125262	3/31/2024	Electronics	$ 13,336.00	$ 14,002.80
20	Walmart	5875491832	3/31/2024	Sports	$ 36,549.00	$ 38,376.45
21	Walmart	3594253512	3/31/2024	Groceries	$ 22,754.00	$ 23,891.70
22	K*Mart	1	10/31/2023	Groceries	$ 1.00	$ 7.00
23	K*Mart	2	10/31/2023	Groceries	$ 2.00	$ 8.00
24	K*Mart	3	10/31/2023	Sports	$ 3.00	$ 9.00
25	K*Mart	4	2/29/2024	Sports	$ 4.00	$ 10.00
26	K*Mart	5	2/29/2024	Electronics	$ 5.00	$ 11.00
27	K*Mart	6	2/29/2024	Electronics	$ 6.00	$ 12.00
28
29
30
Sheet9

Cell Formulas
Range	Formula
I3:J12	I3	=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:Q2	K2	=TRANSPOSE(SORT(UNIQUE((FILTER(C2:C30,C2:C30<>"")))))
K3:Q12	K3	=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
Cell	Condition		Cell Format	Stop If True
I:I	Cell Value	contains "Total"	text	NO
I1:U2	Expression	=(I$2<>"")	text	NO