Index or SumIf on here..

usnapoleon

Board Regular
Joined
May 22, 2014
Messages
110
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJ
8MethodTypeSales TicketsSales Ticket.FeeSales SumRefunds TicketsRefunds Ticket.FeeRefunds SumTotal
9ExchangeCreditCardVISA$5.00$0.00$5.00$0.00$0.00$0.00$5.00
10ExchangeTotal:$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!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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?
 
Upvote 0
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:

1719872421843.png


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
ABCDEFGHIJKL
2$0.00
3Report Date:7/1/2024 13:04
4Event/Performances:All Performances
5Transaction Date Range:06/01/2024 12:00:00 AM - 06/30/2024 11:59:59 PM
6Performance Date Range:All Time
7
8MethodTypeSales TicketsSales Ticket.FeeSales SumRefunds TicketsRefunds Ticket.FeeRefunds SumTotal
9Anthony the Magic!CreditCardVISA$852.70$42.00$894.70$0.00$0.00$0.00$894.70
10Anthony the Magic!Total:$852.70$42.00$894.70$0.00$0.00$0.00$894.70
11ExchangeCreditCardVISA$5.00$0.00$5.00$0.00$0.00$0.00$5.00
12ExchangeTotal:$5.00$0.00$5.00$0.00$0.00$0.00$5.00
13FABBABoxOfficeExchange Payment$0.00$0.00$0.00$72.00$0.00$72.00($72.00)
14FABBABoxOfficeVoucher$82.05$0.00$82.05$0.00$0.00$0.00$82.05
15FABBACreditCardAMEX$369.60$14.00$383.60$0.00$0.00$0.00$383.60
16FABBACreditCardDiscover$84.00$4.00$88.00$0.00$0.00$0.00$88.00
17FABBACreditCardMasterCard$1,512.00$66.00$1,578.00$0.00$0.00$0.00$1,578.00
18FABBACreditCardVISA$6,602.40$218.00$6,820.40$3.60$0.00$3.60$6,816.80
19FABBATotal:$8,650.05$302.00$8,952.05$75.60$0.00$75.60$8,876.45
20Fortunate Son - A Tribute to Creedence Clearwater RevivalCreditCardMasterCard$84.00$4.00$88.00$0.00$0.00$0.00$88.00
ShoWare Rec Report 06.24 Perfor
Cell Formulas
RangeFormula
L2L2=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
NameRefers ToCells
'ShoWare Rec Report 06.24 Perfor'!_FilterDatabase='ShoWare Rec Report 06.24 Perfor'!$A$8:$J$49L2
 
Upvote 0
Sounds like you just want to extract the total rows. Try:
Book1 (version 2).xlsb
ABCDEFGHIJKL
1
2Anthony the Magic!$ 894.70
3Report Date:45474.54444Exchange$ 5.00
4Event/Performances:All PerformancesFABBA$ 8,876.45
5Transaction Date Range:06/01/2024 12:00:00 AM - 06/30/2024 11:59:59 PM
6Performance Date Range:All Time
7
8MethodTypeSales TicketsSales Ticket.FeeSales SumRefunds TicketsRefunds Ticket.FeeRefunds SumTotal
9Anthony the Magic!CreditCardVISA852.742894.7000894.7
10Anthony the Magic!Total:852.742894.7000894.7
11ExchangeCreditCardVISA5050005
12ExchangeTotal:5050005
13FABBABoxOfficeExchange Payment00072072-72
14FABBABoxOfficeVoucher82.05082.0500082.05
15FABBACreditCardAMEX369.614383.6000383.6
16FABBACreditCardDiscover8448800088
17FABBACreditCardMasterCard15126615780001578
18FABBACreditCardVISA6602.42186820.43.603.66816.8
19FABBATotal:8650.053028952.0575.6075.68876.45
20Fortunate Son - A Tribute to Creedence Clearwater RevivalCreditCardMasterCard8448800088
Sheet4
Cell Formulas
RangeFormula
K2:L4K2=CHOOSECOLS(FILTER(A9:J20,B9:B20="Total:"),1,-1)
Dynamic array formulas.
 
Upvote 0
@Cubist
Not quite. The Total needs to be based on the show. My spreadsheet on another tab looks like this:

1719878493421.png


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
1719879421567.png
 
Upvote 0
Slight modification to the formula. Suppose the values in K are the listed values.
Book1 (version 2).xlsb
KL
2Anthony the Magic!$ 894.70
3Exchange$ 5.00
4FABBA$ 8,876.45
5Fortunate Son - A Tribute to Creedence Clearwater Revival$ -
Sheet4
Cell Formulas
RangeFormula
L2:L5L2=FILTER($J$9:$J$20,($A$9:$A$20=K2)*($B$9:$B$20="Total:"),0)
 
Upvote 0
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.
 
Upvote 0
I've arbitrarily chosen Z but you can do whatever is necessary.
Book1 (version 2).xlsb
LM
1
2Anthony the Magic!$ 894.70
3Exchange$ 5.00
4FABBA$ 8,876.45
5Fortunate Son - A Tribute to Creedence Clearwater Revival$ -
Sheet4
Cell Formulas
RangeFormula
M2:M5M2=FILTER(CHOOSECOLS($A$8:$Z$20,XMATCH("Total",$8:$8)),($A$8:$A$20=L2)*($B$8:$B$20="Total:"),0)
 
Last edited:
Upvote 0
Solution
@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!
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,155
Members
453,021
Latest member
Justyna P

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