Index and Match for the sum of multiple cells

[usnapoleon]

Hello and thank you in advance for looking this over. I am having difficulty in creating a formula that will add up multiple cells that all relate to credit card totals. The data is on a tab called "Square Input", and so far this is my formula

=IFERROR(INDEX('Square Input'!J$18:J$45,MATCH(1,('Square Input'!A$18:A$45="The Market")*('Square Input'!B$48:B$58=G7),0)),0)

This part of the formula needs to change -- ('Square Input'!B$48:B$58=G7) --- , but I do not know how to change it to sum up multiple totals from different cells. I'll share the image to see.

So looking at the image, I am looking at the data from rows 19-42, as it shows data from 2 locations: Market & the Blue River Lounge.
For each location, I want totals for each settlement type: Card, Cash, House Account, Gift Card. Card though has multiple lines of data, and that is my challenge. For Market, Card is a combination of rows 19-24 & row 28.

I originally had a simple formula for the sum of those specific rows but recently the system reports pulled a fast one on me and messed with the row order, so my formulas were pointed at rows not related to Card totals. For this reason I want to do an Index and Match to look at the descriptions in column B, and returning the summation of the totals in column J. I just dont know how. I'm open to ideas. I thought "hey, maybe any cell in column B with 'card' in it, but there is Gift Card which would muck up that. So I'm a bit lost for ideas and need your help.

Thank you again. It's been a very long time since I needed help and I've always come here and I'm sorry if I'm so rusty in posting that I did any of this incorrectly.

 

[etaf]

would a SUMIFS() work ???
with a LEFT(cell, 4) = "CARD"
what version of excel do you have

Row 28 - does not have card in , it has CASH APP ???

cannot see whats in G7

so you could use sumifs( using Market & CARD ) + sumifs( Market and CASH)

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[usnapoleon]

Night Audit Spreadsheet Breck BLANK Updated.xlsx
	A	B	C	D	E
1	Breckenridge The Market
2	Tips	0.00		A&G	0.00
3	Tax	#REF!		Cash	0.00
4	Food	0.00		Gift Certificate	0.00
5	Beverages	0.00		Payment Card	97.51
6	Merchandise	0.00		Room Charge	16.61
7	Activities	0.00		Voucher	0.00
8	Other Categories	0.00		Check	0.00
9				Credit Card Fees	0.00
10	Unclassified
11		#REF!			114.12
12
13
14
15
16	Display By Location
17	Filtered By Location: Blue River Lounge, The Market
18	Location	Payment Method	Net Total
19	The Market	Card - Dipped (Chip)	$11.37
20	The Market	Card - Tapped (Contactless)	$86.14
21	The Market	Card - Swiped	$0.00
22	The Market	Card - Keyed	$0.00
23	The Market	Card on File	$0.00
24	The Market	Card - Other	$0.00
25	The Market	Cash	$0.00
26	The Market	Gift Card	$0.00
27	The Market	Other	$0.00
28	The Market	Cash App	$0.00
29	The Market	Open Ticket	$0.00
30	The Market	House Account	$16.61
Square Input

Cell Formulas
Range	Formula
E3:E4	E3	='Square Input'!C25
E5	E5	=SUM('Square Input'!C19:C24)+'Square Input'!C28
E6	E6	='Square Input'!C30
E7	E7	='Square Input'!C27
B2	B2	='Square Input'!F12
B3	B3	='Square Input'!#REF!
B4:B8	B4	=IFERROR(INDEX('Square Input'!F$31:F$34,MATCH(1,('Square Input'!A$31:A$34="The Market")*('Square Input'!B$31:B$34=A4),0)),0)
E9	E9	=-'Square Input'!I12
B11,E11	B11	=SUM(B2:B10)

 

[usnapoleon]

would a SUMIFS() work ???
with a LEFT(cell, 4) = "CARD"
what version of excel do you have

Row 28 - does not have card in , it has CASH APP ???

cannot see whats in G7

so you could use sumifs( using Market & CARD ) + sumifs( Market and CASH)

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

This is Office 16, from my company

Thank you! I posted teh XL2BB. I cut out a lot of the fluff to make it simple.

The top part is usually on a different tab, that is why you see 'Square Input' addressed in the formulas.

I color coordinated the cells that are related to each other. Thus, the "Payment Card" highlighted yellow is meant to have a formula that sums up all the yellow highlighted settlement types below, and so forth.

I hope this helps you out. The option I am dreading on using is a super long formula that includes a Match for each of the settlement types... in other words, a long formula summation formula that says to match "Card - Dipped (Chip)", "Card - Tapped (Contactless)" and so forth. It would be messy for sure!

 

[etaf]

can you change the names in D2 to D9 to match the elements in B19 to B30

then you could use a SUMIF()
=SUMIF($B$19:$B$30,D3,$C$19:$C$30)
and simple refer to D2:D9 as the criteria

for the part with card then
=SUMPRODUCT((LEFT(B19:B30,4)=D5)*(C19:C30))
will add add anything that starts with card
add then you will need to add
SUMIF($B$19:$B$30,"Cash App",$C$19:$C$30)
for cash app
=SUMPRODUCT((LEFT(B19:B30,4)=D5)*(C19:C30))+SUMIF($B$19:$B$30,"Cash App",$C$19:$C$30)

otherwise , you will need to hard code the criteria
like
=SUMIF($B$19:$B$30,"house Account",$C$19:$C$30)

What are all the possible groupings
for example
Payment Card
is the Left part of all the transaction to just pull out CARD
and then we add a sumif() for "Cash App"

Cash
is everything that just has CASH as an entry only

we would probably need to see all the possibly entries and the grouping you need
perhaps in a table

Book1
	A	B	C	D	E
1	Breckenridge The Market
2	Tips	#REF!		A&G	0
3	Tax	#REF!		Cash	100
4	Food	0		Gift Card	111
5	Beverages	0		Card	444683429.5
6	Merchandise	0		House Account	16.61
7	Activities	0		Voucher	0
8	Other Categories	0		Check	0
9				Credit Card Fees	0
10	Unclassified				0
11		#REF!			0
12
13
14
15
16	Display By Location
17	Filtered By Location: Blue River Lounge, The Market
18	Location	Payment Method	Net Total
19	The Market	Card - Dipped (Chip)	11.37
20	The Market	Card - Tapped (Contactless)	86.14
21	The Market	Card - Swiped	11111
22	The Market	Card - Keyed	222222
23	The Market	Card on File	2222
24	The Market	Card - Other	3333
25	The Market	Cash	100
26	The Market	Gift Card	111
27	The Market	Other	0
28	The Market	Cash App	444444444
29	The Market	Open Ticket	0
30	The Market	House Account	16.61
Sheet2

Cell Formulas
Range	Formula
B2	B2	='Square Input'!F12
B3	B3	='Square Input'!#REF!
B4:B8	B4	=IFERROR(INDEX('Square Input'!F$31:F$34,MATCH(1,('Square Input'!A$31:A$34="The Market")*('Square Input'!B$31:B$34=A4),0)),0)
B11	B11	=SUM(B2:B10)
E3:E4,E6:E11	E3	=SUMIF($B$19:$B$30,D3,$C$19:$C$30)
E5	E5	=SUMPRODUCT((LEFT(B19:B30,4)=D5)*(C19:C30))+SUMIF($B$19:$B$30,"Cash App",$C$19:$C$30)

 

[usnapoleon]

can you change the names in D2 to D9 to match the elements in B19 to B30

then you could use a SUMIF()
=SUMIF($B$19:$B$30,D3,$C$19:$C$30)
and simple refer to D2:D9 as the criteria

for the part with card then
=SUMPRODUCT((LEFT(B19:B30,4)=D5)*(C19:C30))
will add add anything that starts with card
add then you will need to add
SUMIF($B$19:$B$30,"Cash App",$C$19:$C$30)
for cash app
=SUMPRODUCT((LEFT(B19:B30,4)=D5)*(C19:C30))+SUMIF($B$19:$B$30,"Cash App",$C$19:$C$30)

Hi Etaf
This worked perfect, you are a genius! I could and did change the names in D2-9 to match the elements... Cash, Card, Gift Card, House Account, Other... That part made sense because I did the same for the revenues you see in A4-A8, but those formulas were easy to make because that part of the spreadsheet is more custom than the payment section we're dealing with.

I also used the formula given =SUMPRODUCT((LEFT(B19:B30,4)=D5)*(C19:C30))+SUMIF($B$19:$B$30,"Cash App",$C$19:$C$30) and it worked.

I need to be able to modify your formula for one additional attribute. We have 2 outlets. My XL2BB only showed "The Market" because you recommended I keep it simple, but in my actual spreadsheet directly below it is the 2nd outlet "Blue River Lounge" with exact same categories of payment types. I need to insert a Match somewhere in the formula, looking at column A "The Market" & "Blue River Lounge", but my brain isn't functioning in where I should put it.

Can I please ask for your help one last time?

 

[etaf]

we can an extra criteria for "the market" and "Blue River Lounge"

But the you would use SUMIFS()

which has the sumrange in different postion
=SUMIFS($C$19:$C$30,$B$19:$B$30,"Cash App",$B$19:$B$30,"the market")

instead of "the market - you could use a cell reference

with
=SUMPRODUCT((LEFT(B19:B30,4)=D5)*(A19:A30="the Market")*(C19:C30))

you could have a header for "the market or Blue River

Book2
	A	B	C	D	E	F
1	Breckenridge The Market
2					The Market	Blue River Lounge
3	Tips	#REF!		A&G	0
4	Tax	#REF!		Cash	100	1000
5	Food	0		Gift Card	111
6	Beverages	0		Card	444672318.5	11111
7	Merchandise	0		House Account	16.61
8	Activities	0		Voucher	0
9	Other Categories	0		Check	0
10				Credit Card Fees	0
11	Unclassified				0
12		#REF!			0
13
14
15
16
17	Display By Location
18	Filtered By Location: Blue River Lounge, The Market
19	Location	Payment Method	Net Total
20	The Market	Card - Dipped (Chip)	11.37
21	The Market	Card - Tapped (Contactless)	86.14
22	Blue River Lounge	Card - Swiped	11111
23	The Market	Card - Keyed	222222
24	The Market	Card on File	2222
25	The Market	Card - Other	3333
26	The Market	Cash	100
27	The Market	Gift Card	111
28	Blue River Lounge	Cash	1000
29	The Market	Cash App	444444444
30	The Market	Open Ticket	0
31	The Market	House Account	16.61
Sheet2

Cell Formulas
Range	Formula
F4	F4	=SUMIFS($C$20:$C$31,$B$20:$B$31,D4,$A$20:$A$31,$F$2)
F6	F6	=SUMPRODUCT((LEFT(B20:B31,4)=D6)*($A$20:$A$31=$F$2)*(C20:C31))+SUMIFS($C$20:$C$31,$B$20:$B$31,"Cash App",$A$20:$A$31,$F$2)
B3	B3	='Square Input'!F12
B4	B4	='Square Input'!#REF!
B5:B9	B5	=IFERROR(INDEX('Square Input'!F$31:F$34,MATCH(1,('Square Input'!A$31:A$34="The Market")*('Square Input'!B$31:B$34=A5),0)),0)
B12	B12	=SUM(B3:B11)
E4:E5,E7	E4	=SUMIFS($C$20:$C$31,$B$20:$B$31,D4,$A$20:$A$31,$E$2)
E6	E6	=SUMPRODUCT((LEFT(B20:B31,4)=D6)*($A$20:$A$31=$E$2)*(C20:C31))+SUMIFS($C$20:$C$31,$B$20:$B$31,"Cash App",$A$20:$A$31,$E$2)
E8:E12	E8	=SUMIF($B$20:$B$31,D8,$C$20:$C$31)

But can you perhaps post a XL2BB - with the layout of the data , so we can see best way to go and if it would be better and possible to change layout