Formula returning #value, unsure why

[usnapoleon]

I received the following formula on a previous thread and it has worked amazingly. However, we had an issue one day where revenue reporting at 1 of our 2 outlets didnt post. The outlets are Market and Lounge and we were missing Lounge. On the excel export, the Market data got shuffled upwards in respect to the range of data. I hope that makes sense. Essentially, purely as an example, if Lounge was on row 5 normally and Market was row 6, then Market got moved up to 5 because Lounge was missing. So I need to re-write formulas to anticipate issues like this. Here is the existing formula:

=SUMPRODUCT((LEFT(B48:B73,4)=D19)*($A$48:$A$73="Blue River Lounge")*(J48:J73))+SUMIFS($J$48:$J$73,$B$48:$B$73,"Cash App",$A$48:$A$73,"Blue River Lounge")

and the context of where it was at, specifically the 'card' formula

Audit Breck Template 072023 BLANK.xlsx
	A	B	C	D	E
15	Breckenridge Blue River Lounge
16	Tips	84.08		A&G	0.00
17	Tax	48.44		Cash	0.00
18	Food	288.50		Gift Card	0.00
19	Beverages	73.50		Card	186.36
20	Alcohol	184.00		House Account	430.33
21	Activites	0.00		Other	55.52
22	Other Categories	0.00		Check	0.00
23				Credit Card Fees	6.31
24	Unclassified
25		678.52			678.52
31

Cell Formulas
Range	Formula
E17:E18,E20:E21	E17	=IFERROR(INDEX(J$46:J$73,MATCH(1,(A$46:A$73="Blue River Lounge")*(B$46:B$73=D17),0)),0)
E19	E19	=SUMPRODUCT((LEFT(B48:B73,4)=D19)*($A$48:$A$73="Blue River Lounge")*(J48:J73))+SUMIFS($J$48:$J$73,$B$48:$B$73,"Cash App",$A$48:$A$73,"Blue River Lounge")
B16	B16	=H42
B17	B17	=G42
B18:B22	B18	=IFERROR(INDEX(H$30:H$90,MATCH(1,(A$30:A$90="Blue River Lounge")*(B$30:B$90=A18),0)),0)
E23	E23	=-K42
B25,E25	B25	=SUM(B16:B24)

I thought I could expand the range of the formula to begin at row 30 instead of 48, so I did a find/replace and all the formulas for other settlement types were fine, giving the same results as before, but Card came up with a #VALUE and I dont know why.

I'm using a normal day's testing data first, before I try the Market-only data. So the data below will have both Market and Lounge. Afterall, if it cant work normally, then it doesnt matter right?! After I get this figured out, I'll test the Market-only day's data.

Here is the data that the formula is looking at:

Audit Breck Template 072023 BLANK.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
30	Key Stats
31	Filtered By Location: Blue River Lounge, The Market
32	Sales	43
33	Avg. Gross Sale	$15.29
34	Gross Sales	$657.50
35	Total Collected	$754.74
36
37	Sales Summary
38	Display By Location
39	Filtered By Location: Blue River Lounge, The Market
40	Sales Summary Displayed by Location	Gross Sales	Refunds	Discounts & Comps	Net Sales	Gift Card Sales	Tax	Tip	Partial Refunds	Total Collected	Fees	Net Total
41	The Market	$96.00	$0.00	($26.00)	$70.00	$0.00	$6.22	$0.00	$0.00	$76.22	($3.45)	$72.77
42	Blue River Lounge	$561.50	$0.00	($15.50)	$546.00	$0.00	$48.44	$84.08	$0.00	$678.52	($6.31)	$672.21
43
44	Payment Methods
45	Display By Location
46	Filtered By Location: Blue River Lounge, The Market
47	Location	Payment Method	Payments	Refunds	Payment Amount	Refund Amount	Tips	Total Collected	Fees	Net Total
48	The Market	Card - Dipped (Chip)	1	0	$21.78	$0.00	$0.00	$21.78	($0.67)	$21.11
49	The Market	Card - Tapped (Contactless)	14	0	$44.10	$0.00	$0.00	$44.10	($2.55)	$41.55
50	The Market	Card - Swiped	1	0	$4.90	$0.00	$0.00	$4.90	($0.23)	$4.67
51	The Market	Card - Keyed	0	0	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
52	The Market	Card on File	0	0	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
53	The Market	Card - Other	0	0	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
54	The Market	Cash	0	0	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
55	The Market	Gift Card	0	0	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
56	The Market	Other	0	0	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
57	The Market	Cash App	0	0	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
58	The Market	Open Ticket	0	0	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
59	The Market	House Account	1	0	$5.44	$0.00	$0.00	$5.44	$0.00	$5.44
60	Blue River Lounge	Card - Dipped (Chip)	4	0	$56.28	$0.00	$8.38	$56.28	($1.86)	$54.42
61	Blue River Lounge	Card - Tapped (Contactless)	8	0	$127.68	$0.00	$5.75	$127.68	($4.12)	$123.56
62	Blue River Lounge	Card - Swiped	1	0	$8.71	$0.00	$0.00	$8.71	($0.33)	$8.38
63	Blue River Lounge	Card - Keyed	0	0	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
64	Blue River Lounge	Card on File	0	0	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
65	Blue River Lounge	Card - Other	0	0	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
66	Blue River Lounge	Cash	0	0	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
67	Blue River Lounge	Gift Card	0	0	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
68	Blue River Lounge	Other	2	0	$55.52	$0.00	$0.00	$55.52	$0.00	$55.52
69	Blue River Lounge	Cash App	0	0	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
70	Blue River Lounge	Open Ticket	0	0	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
71	Blue River Lounge	House Account	11	0	$430.33	$0.00	$69.95	$430.33	$0.00	$430.33
31

 

[ExceLoki]

which version of excel are you using?
you can add to your profile to assist with future posts.

 

[usnapoleon]

which version of excel are you using?
you can add to your profile to assist with future posts.

Thanks! This is from Office 365. I'll input this in my profile too. Thanks for the advice.

 

[ExceLoki]

here's a quick explanation as to why it's happening
i have broken down the parts of your formula into : b, a & j. result is when you multiply them together, and then the sumproduct is when you insert the result into the function.

basically it doesnt like multiplying words and numbers (false are seen as 0)
--------------

Book1
	M	N	O	P	Q
28	column b	column a	column j	result	sumproduct
29
30	FALSE	FALSE	0	0	0
31	FALSE	FALSE	0	0	0
32	FALSE	FALSE	0	0	0
33	FALSE	FALSE	0	0	0
34	FALSE	FALSE	0	0	0
35	FALSE	FALSE	0	0	0
36	FALSE	FALSE	0	0	0
37	FALSE	FALSE	0	0	0
38	FALSE	FALSE	0	0	0
39	FALSE	FALSE	0	0	0
40	FALSE	FALSE	Total Collected	#VALUE!	#VALUE!
41	FALSE	FALSE	76.22	0	76.22
42	FALSE	TRUE	678.52	0	678.52
Sheet2

Cell Formulas
Range	Formula
M30:M73	M30	=(LEFT(B30:B73,4)=D19)
N30:N73	N30	=($A$30:$A$73="Blue River Lounge")
O30:O73	O30	=(J30:J73)
P30:P73	P30	=(LEFT(B30:B73,4)=D19)*($A$30:$A$73="Blue River Lounge")*(J30:J73)
Q30:Q42	Q30	=SUMPRODUCT(M30:P30)
Dynamic array formulas.

 

[ExceLoki]

try using what i have in column f below, your numbers in column b dont match mine as dont have the data those formulas are pulling from on my sheet
------------------

Book1
	A	B	C	D	E	F
15	Breckenridge Blue River Lounge
16	Tips	84.08		A&G	0	0
17	Tax	48.44		Cash	0	0
18	Food	0		Gift Card	0	0
19	Beverages	0		Card	#VALUE!	186.36
20	Alcohol	0		House Account	430.33	430.33
21	Activites	0		Other	55.52	55.52
22	Other Categories	0		Check	0	0
23				Credit Card Fees	6.31
24	Unclassified
25		132.52
Sheet2

Cell Formulas
Range	Formula
E17:E18,E20:E21	E17	=IFERROR(INDEX(J$46:J$73,MATCH(1,(A$46:A$73="Blue River Lounge")*(B$46:B$73=D17),0)),0)
E19	E19	=SUMPRODUCT((LEFT(B30:B73,4)=D19)*($A$30:$A$73="Blue River Lounge")*(J30:J73))+SUMIFS($J$30:$J$73,$B$30:$B$73,"Cash App",$A$30:$A$73,"Blue River Lounge")
B16	B16	=H42
B17	B17	=G42
B18:B22	B18	=IFERROR(INDEX(H$30:H$90,MATCH(1,(A$30:A$90="Blue River Lounge")*(B$30:B$90=A18),0)),0)
F16:F18,F20:F22	F16	=IFERROR(SUM(FILTER($J$30:$J$71,($B$30:$B$71=D16)*($A$30:$A$71=TEXTAFTER($A$15," ",1)),0)),0)
F19	F19	=SUM(FILTER($J$30:$J$71,(LEFT($B$30:$B$71,4)=D19)*($A$30:$A$71=TEXTAFTER($A$15," ",1)),0))
E23	E23	=-K42
B25	B25	=SUM(B16:B24)

 

[usnapoleon]

try using what i have in column f below, your numbers in column b dont match mine as dont have the data those formulas are pulling from on my sheet
------------------

Book1
	A	B	C	D	E	F
15	Breckenridge Blue River Lounge
16	Tips	84.08		A&G	0	0
17	Tax	48.44		Cash	0	0
18	Food	0		Gift Card	0	0
19	Beverages	0		Card	#VALUE!	186.36
20	Alcohol	0		House Account	430.33	430.33
21	Activites	0		Other	55.52	55.52
22	Other Categories	0		Check	0	0
23				Credit Card Fees	6.31
24	Unclassified
25		132.52
Sheet2

Cell Formulas
Range	Formula
E17:E18,E20:E21	E17	=IFERROR(INDEX(J$46:J$73,MATCH(1,(A$46:A$73="Blue River Lounge")*(B$46:B$73=D17),0)),0)
E19	E19	=SUMPRODUCT((LEFT(B30:B73,4)=D19)*($A$30:$A$73="Blue River Lounge")*(J30:J73))+SUMIFS($J$30:$J$73,$B$30:$B$73,"Cash App",$A$30:$A$73,"Blue River Lounge")
B16	B16	=H42
B17	B17	=G42
B18:B22	B18	=IFERROR(INDEX(H$30:H$90,MATCH(1,(A$30:A$90="Blue River Lounge")*(B$30:B$90=A18),0)),0)
F16:F18,F20:F22	F16	=IFERROR(SUM(FILTER($J$30:$J$71,($B$30:$B$71=D16)*($A$30:$A$71=TEXTAFTER($A$15," ",1)),0)),0)
F19	F19	=SUM(FILTER($J$30:$J$71,(LEFT($B$30:$B$71,4)=D19)*($A$30:$A$71=TEXTAFTER($A$15," ",1)),0))
E23	E23	=-K42
B25	B25	=SUM(B16:B24)

This worked!!! I need to dissect it a bit, which is a challenge when I'm on a conference call! How do I integrate that 'Cash App' total into it? I know its called 'cash' app, but its really running off credit cards so it's related.

Oh man, I am excited to dig into this! thank you!

 

[ExceLoki]

"Card - Cash App", anything that starts with first 4 characters of "Card" will be together

consider marking it as a solution to help others in the future.

no prob, happy to help

 

[usnapoleon]

Awesome. I've never used Filter before, I'm reading up on it now. I see how its working, making the sum of the array of $$ as it is including anything in column b with the first 4 digits matching d19 ('card') and column a matching A19 for the last 15 letters. Very interesting!!! Thank you again!!!