Index or SumIf on here..

[usnapoleon]

I am trying to get a formula that looks over a whole page of data, and pulls just 1 number based on some matching criteria. The name in column A, the 'Total:' in column B, and it reports the 'total' amount based on wherever 'total' is in row 8

06.24 Reconciliation Report.xlsx
	A	B	C	D	E	F	G	H	I	J
8		Method	Type	Sales Tickets	Sales Ticket.Fee	Sales Sum	Refunds Tickets	Refunds Ticket.Fee	Refunds Sum	Total
9	Exchange	CreditCard	VISA	$5.00	$0.00	$5.00	$0.00	$0.00	$0.00	$5.00
10	Exchange	Total:		$5.00	$0.00	$5.00	$0.00	$0.00	$0.00	$5.00
Report_ReconciliationReport_7-1

I have this sample here. Overall, there's more groups than just 'Exchange', I just wanted to keep this visually simple. And I want to account for Total in column J possibly moving to different columns in case the data provided ever changes - so if they add a column and Total goes from Column J to column K instead, I want this formula to be able to catch it.

I have used SumIf and Index before when trying to match certain criteria, but I wasn't sure how to approach this one.

Thanks!

 

[awoohaw]

Do you have just one header row (row 8)? Is the total row that you're trying to match always in Column B? Is column C on the 'total' row always blank?

 

[usnapoleon]

Do you have just one header row (row 8)? Is the total row that you're trying to match always in Column B? Is column C on the 'total' row always blank?

Assuming I understand your question correctly, yes, just the 1 header row. Always seems to be in row 8 each month.

Yes, the 'Total:' is always column B. That column B, the 'method' column... it is a collection of payment types, such as cash, credit card, etc. All of those can be in Column B for the 'method' of payment. I only care about the 'Total:' because that's the row that will sum up all the methods for the particular item in column A. Another example is below, but I did it via snip, just so you can see:

And to your third question... yes, it seems that the 'Type' is always blank in column C. Good eye. It makes sense, because type is the more specific payment type, like we can see in the snip above, so a total line wouldnt necessarily have a Type, and thus, blank.




I gave it a 'college try' but it failed. In L2 I put in a formula but it didnt give the results I wanted.

One thing I want to point out about the formula, it originates in a different tab, and when it didnt work, I copied it over to this tab so I'd have a better view of where it's directly pointing to - this is why you'll see the tab's name so often in it. When I figure out what is wrong or get a new/different formula, it will go back to the different tab.​

Looking at it, I thought it was good ... but arg...

DSJ-063024 WRSD Theatre Only JB.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
2												$0.00
3	Report Date:	7/1/2024 13:04
4	Event/Performances:	All Performances
5	Transaction Date Range:	06/01/2024 12:00:00 AM - 06/30/2024 11:59:59 PM
6	Performance Date Range:	All Time
7
8		Method	Type	Sales Tickets	Sales Ticket.Fee	Sales Sum	Refunds Tickets	Refunds Ticket.Fee	Refunds Sum	Total
9	Anthony the Magic!	CreditCard	VISA	$852.70	$42.00	$894.70	$0.00	$0.00	$0.00	$894.70
10	Anthony the Magic!	Total:		$852.70	$42.00	$894.70	$0.00	$0.00	$0.00	$894.70
11	Exchange	CreditCard	VISA	$5.00	$0.00	$5.00	$0.00	$0.00	$0.00	$5.00
12	Exchange	Total:		$5.00	$0.00	$5.00	$0.00	$0.00	$0.00	$5.00
13	FABBA	BoxOffice	Exchange Payment	$0.00	$0.00	$0.00	$72.00	$0.00	$72.00	($72.00)
14	FABBA	BoxOffice	Voucher	$82.05	$0.00	$82.05	$0.00	$0.00	$0.00	$82.05
15	FABBA	CreditCard	AMEX	$369.60	$14.00	$383.60	$0.00	$0.00	$0.00	$383.60
16	FABBA	CreditCard	Discover	$84.00	$4.00	$88.00	$0.00	$0.00	$0.00	$88.00
17	FABBA	CreditCard	MasterCard	$1,512.00	$66.00	$1,578.00	$0.00	$0.00	$0.00	$1,578.00
18	FABBA	CreditCard	VISA	$6,602.40	$218.00	$6,820.40	$3.60	$0.00	$3.60	$6,816.80
19	FABBA	Total:		$8,650.05	$302.00	$8,952.05	$75.60	$0.00	$75.60	$8,876.45
20	Fortunate Son - A Tribute to Creedence Clearwater Revival	CreditCard	MasterCard	$84.00	$4.00	$88.00	$0.00	$0.00	$0.00	$88.00
ShoWare Rec Report 06.24 Perfor

Cell Formulas
Range	Formula
L2	L2	=IFERROR(INDEX('ShoWare Rec Report 06.24 Perfor'!$A$8:$M$118,MATCH(A19,'ShoWare Rec Report 06.24 Perfor'!$A$8:$A$118,0),MATCH("Total:",'ShoWare Rec Report 06.24 Perfor'!$B$8:$B$118,0),MATCH("Total",'ShoWare Rec Report 06.24 Perfor'!$A$8:$M$8,0)),0)

Named Ranges
Name	Refers To	Cells
'ShoWare Rec Report 06.24 Perfor'!_FilterDatabase	='ShoWare Rec Report 06.24 Perfor'!$A$8:$J$49	L2

 

[Cubist]

Sounds like you just want to extract the total rows. Try:

Book1 (version 2).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L
1
2											Anthony the Magic!	$ 894.70
3	Report Date:	45474.54444									Exchange	$ 5.00
4	Event/Performances:	All Performances									FABBA	$ 8,876.45
5	Transaction Date Range:	06/01/2024 12:00:00 AM - 06/30/2024 11:59:59 PM
6	Performance Date Range:	All Time
7
8		Method	Type	Sales Tickets	Sales Ticket.Fee	Sales Sum	Refunds Tickets	Refunds Ticket.Fee	Refunds Sum	Total
9	Anthony the Magic!	CreditCard	VISA	852.7	42	894.7	0	0	0	894.7
10	Anthony the Magic!	Total:		852.7	42	894.7	0	0	0	894.7
11	Exchange	CreditCard	VISA	5	0	5	0	0	0	5
12	Exchange	Total:		5	0	5	0	0	0	5
13	FABBA	BoxOffice	Exchange Payment	0	0	0	72	0	72	-72
14	FABBA	BoxOffice	Voucher	82.05	0	82.05	0	0	0	82.05
15	FABBA	CreditCard	AMEX	369.6	14	383.6	0	0	0	383.6
16	FABBA	CreditCard	Discover	84	4	88	0	0	0	88
17	FABBA	CreditCard	MasterCard	1512	66	1578	0	0	0	1578
18	FABBA	CreditCard	VISA	6602.4	218	6820.4	3.6	0	3.6	6816.8
19	FABBA	Total:		8650.05	302	8952.05	75.6	0	75.6	8876.45
20	Fortunate Son - A Tribute to Creedence Clearwater Revival	CreditCard	MasterCard	84	4	88	0	0	0	88
Sheet4

Cell Formulas
Range	Formula
K2:L4	K2	=CHOOSECOLS(FILTER(A9:J20,B9:B20="Total:"),1,-1)
Dynamic array formulas.

 

[usnapoleon]

@Cubist
Not quite. The Total needs to be based on the show. My spreadsheet on another tab looks like this:

Above, I have the show names exactly how they are represented in the data report. As such, the formula is meant to look through the data, matching to the show name, and specifically going for that Total column's number.

That is what my intention with this formula is:

=IFERROR(INDEX('ShoWare Rec Report 06.24 Perfor'!$A$8:$M$118,MATCH(A20,'ShoWare Rec Report 06.24 Perfor'!$A$8:$A$118,0),MATCH("Total:",'ShoWare Rec Report 06.24 Perfor'!$B$8:$B$118,0),MATCH("Total",'ShoWare Rec Report 06.24 Perfor'!$A$8:$M$8,0)),0)
Like I said though, it's not working.

I can always do this:
=IFERROR(SUMIFS('ShoWare Rec Report 06.24 Perfor'!$J:$J,'ShoWare Rec Report 06.24 Perfor'!$A:$A,$K$4,'ShoWare Rec Report 06.24 Perfor'!$B:$B,"Total:"),0)

And this works, but if the columns change (trying to bulletproof this for the future) then it wont work. I mostly anticipate the J column potentially changing

 

[Cubist]

Slight modification to the formula. Suppose the values in K are the listed values.

Book1 (version 2).xlsb
	K	L
2	Anthony the Magic!	$ 894.70
3	Exchange	$ 5.00
4	FABBA	$ 8,876.45
5	Fortunate Son - A Tribute to Creedence Clearwater Revival	$ -
Sheet4

Cell Formulas
Range	Formula
L2:L5	L2	=FILTER($J$9:$J$20,($A$9:$A$20=K2)*($B$9:$B$20="Total:"),0)

 

[usnapoleon]

I appreciate it, but that formula will have the same problem as mine, if ever any additional columns get added, and the Total column (currently J) shuffles over, then the formula will need to be edited.

 

[Cubist]

I've arbitrarily chosen Z but you can do whatever is necessary.

Book1 (version 2).xlsb
	L	M
1
2	Anthony the Magic!	$ 894.70
3	Exchange	$ 5.00
4	FABBA	$ 8,876.45
5	Fortunate Son - A Tribute to Creedence Clearwater Revival	$ -
Sheet4

Cell Formulas
Range	Formula
M2:M5	M2	=FILTER(CHOOSECOLS($A$8:$Z$20,XMATCH("Total",$8:$8)),($A$8:$A$20=L2)*($B$8:$B$20="Total:"),0)

 

[usnapoleon]

@Cubist That works!! I havent used xmatch before, nor choosecols. Thank you for sharing this with me!

That is interesting... im looking at match vs xmatch differences online. This is cool! Thank you!

 

[Cubist]

You're welcome.