Complex sumifs formula with two mappings

[Pilara]

Hi,

I have a problem and i was wandering if it is possible to solve with one formula in one cell.

So i have a data set where i have 3 columns:
1) 18 characters "key" string (which looks something like that ABC123456781234567)
2) different dates
3) amounts (are not formatted as amount, to format it as amount i need to use formula =SUBSTITUTE(amount;".";"")

Data sheet
Col1 Col2 Col3
ABC123456781234567 2022-06-27 37,23
DEF234567893322112 2022-06-22 1.345,34
GHI456789013254672 2022-06-29 456,21
(the real one has around 250 rows)


for the "key" string can be split into 3 parts:
1) letters (ABC) which are corresponding to lets say colour, mapping looks like that:

Sheet1
Col1 Col2
ABC Green
DEF Yellow
GHI White
JKL Orange

2) second part is made of 8 next digits (in this case 12345678) for which i have much longer mapping where those numbers are corresponding with other numbers, so something like that:

Sheet2
Col1 Col2
12345678 11
23456789 12
34567890 13
45678901 11
56789012 13

third part of string is relevant.

What i am trying write is formula that calculates in one cell sum of all lines which from first part of "key" get Green, from second get 11 for only one day.

Any ideas?

I would like to avoid using VBA and adding new helper columns

 

[Eric W]

You can:

Book3
	A	B	C	D	E	F	G	H
1	ABC123456781234567	6/27/2022	37,23		Color	Date	Part 2	Sum
2	DEF234567893322112	6/22/2022	1.345,34		DEF	6/22/2022	12	1345.34
3	GHI456789013254672	6/29/2022	456,21
4
5
6
7	ABC123456781234567	6/27/2022	37.23					Sum
8	DEF234567893322112	6/22/2022	1345.34					1345.34
9	GHI456789013254672	6/29/2022	456.21
10
Sheet20

Cell Formulas
Range	Formula
H2	H2	=SUMPRODUCT(--(LEFT(A1:A4,3)=E2),--(B1:B4=F2),--(RIGHT(A1:A4,2)=G2&""),--("0"&SUBSTITUTE(SUBSTITUTE(C1:C4,".",""),",",".")))
H8	H8	=SUMIFS(C7:C10,A7:A10,E2&"*",B7:B10,F2,A7:A10,"*"&G2)

The H2 formula should do what you want. Use your conversion formula as the last parameter to get the amount. If you want to have "Yellow" in E2, you can just use a VLOOKUP in the formula instead of the fixed E2.

If you can convert the amounts to a proper amount in another column, the formula is much easier (H8).

 

[Pilara]

Sorry, I must have stated incorrectly my problem.
I need to search them by words Green (which is ABC in mapping on sheet1) and numer 11 (which is 12345678 in mapping in sheet2).

Maybe if i do it on more appropriate example it will be easier to understand.

Data sheet is document from bank, in first column is string that contains information about payment type, and store in which payment was made, second is payment date and third is amount paid.

Data sheet
Description Date Amount
ABC123456781234567 2022-06-27 37,23
DEF234567893322112 2022-06-22 1.345,34
GHI456789013254672 2022-06-29 456,21


so the mapping looks something like below:

first 3 letters corresponds to Card type (Visa, Mastercard etc.) following 8 digits is a number of store

Sheet1
Type Card Name
ABC Visa
DEF Maestro
GHI Mastercard
JKL National Bank

This mapping has 4 rows

2) second part is made of 8 next digits (in this case 12345678) for which i have much longer mapping where those numbers are corresponding with other numbers, so something like that: sorry, I just found out that there also might be letters-digits combinations.
From second part that is related to Store number it can as well be 8 letters-digits combination (like in the last row, those letters are not the same as in card type above), which can be mapped to Store number

Sheet2
Col1 Store Number
12345678 Store 11
23456789 Store 12
34567890 Store 13
45678901 Store 11
56789012 Store 13
CSDF123 Store 14

This mapping has more than 250 rows

third part of string is relevant.

What i need to sum is amount of payments made in Store 11 with Visa card on one day.

Then I will be able to make sums for other cards and from other Stores, compare it with some other documents and see if everything was properly booked.

I hope that I made it more clear this time, thank You for help

 

[Eric W]

If I understand correctly, it's still the same basic concept:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ABC123456781234567	6/27/2022	37,23		Card Type	Store	Date	Sum			Type	Card Name		Code	Store Number
2	DEF234567893322112	6/22/2022	1.345,34		Maestro	Store 12	6/22/2022	1345.34			ABC	VISA		12345678	Store 11
3	GHI456789013254672	6/29/2022	456,21								DEF	Maestro		23456789	Store 12
4					DEF	23456789					GHI	Mastercard		34567890	Store 13
5											JKL	National Bank		45678901	Store 14
6														56789012	Store 15
7	ABC123456781234567	6/27/2022	37.23					Sum
8	DEF234567893322112	6/22/2022	1345.34					1345.34
9	GHI456789013254672	6/29/2022	456.21
10
Sheet20

Cell Formulas
Range	Formula
H2	H2	=SUMPRODUCT(--(LEFT(A1:A4,11)=E4&F4),--("0"&SUBSTITUTE(SUBSTITUTE(C1:C4,".",""),",",".")))
E4	E4	=INDEX(Sheet20!K2:K5,MATCH(E2,Sheet20!L2:L5,0))
F4	F4	=INDEX(Sheet20!N2:N5,MATCH(F2,Sheet20!O2:O5,0))
H8	H8	=SUMIFS(C7:C10,A7:A10,E4&F4&"*",B7:B10,G2)

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=$L$2:$L$5
F2	List	=$O$2:$O$6

I put your tables on this sheet for ease of explanation, but you have them on Sheet1. Change Sheet20 to Sheet1 in the formulas in your workbook. I assume you'll have a dropdown in E2 and F2 where you'll pick the card and store, probably using the same tables as the source (which I did here). Then the E4 and F4 formulas look up the corresponding codes. Then the H2 formula uses those values. You could include the E4:F4 formulas in the H2 formula, but that makes it a bit less clear. As before, the H8 formula is just to show that the SUMIFS version is shorter if you can convert the C column to actual numbers.

I'm still confused when you say that the "third part of the string is relevant". I assume that's it a transaction number of some kind, but it's not needed to select the transactions for the total.