Index, SumIf?, Matching

[usnapoleon]

Hello,
I feel I'm going to kick myself when you post a simple fix to my formula...
I have an existing formula that gives me the Discount total for 1 outlet... but I want to modify it to give me total Discounts. I'll show you:

Audit Breck Template REV 05.2024.xlsx
	M	N	O	P	Q
25		Discounts & Comps		-30.8	Variance Check
1

Cell Formulas
Range	Formula
P25	P25	=IFERROR(INDEX($A$40:$Z$43,MATCH("Trails' End Lounge",$A$40:$A$43,0),MATCH(N25,$A$40:$Z$40,0)),0)

It's pulling from this:

Audit Breck Template REV 05.2024.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
37	Sales Summary
38	Display By Location
39	Filtered By Location: The Market, Trails' End Lounge
40	Sales Summary Displayed by Location	Gross Sales	Items	Service Charges	Refunds	Discounts & Comps	Net Sales	Gift Card Sales	Tax	Tip	Partial Refunds	Total Collected	Fees	Net Total
41	The Market	$126.00	$126.00	$0.00	($8.00)	($0.80)	$117.20	$0.00	$10.41	$0.00	$0.00	$127.61	($2.55)	$125.06
42	Trails' End Lounge	$591.00	$591.00	$0.00	$0.00	($30.80)	$560.20	$0.00	$49.72	$77.49	$0.00	$687.41	($12.16)	$675.25
43
1

As per the formula, its correctly grabbing the 'Trails' End Lounge' number, but I dont want it to be so specific. I want it to grab any numbers under that Discounts & Comps column.

Just as an FYI, that formula is as complicated as it is to account for the future possibility that columns may be added which could shift data over. If that ever happens, the formula will still supply the proper numbers, whether the Discounts & Comps were column F (as it currently is) or column J (as an example).

 

[hagia_sofia]

Do you mean something like this?

=SUM(FILTER(B41:N42,B40:N40=N25))

 

[usnapoleon]

Do you mean something like this?

=SUM(FILTER(B41:N42,B40:N40=N25))

See... told you.. I feel stupid, hahaha. Thank you!!!!!!!

 

[smozgur]

See... told you.. I feel stupid, hahaha. Thank you!!!!!!!

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.