Filter and Countifs?

[Limit Braker]

Hi guys,

Im trying to filter to a table some promotions (given that our ERP doesnt have mix and match codes to filter from)

The database im extracting from looks like this:

and the table im filtering it to looks like this:

The colors match the products of the promotion.

So for example.

What if i want to know how many promo2 did store1 sell? witht that given price? (promo2 total price is 25 bucks, how do i sum the values on different rows to add up that criteria and at the same time fulfill all the rest of criterias?)

I did it with Countifs filtering the products from a pivottable so that i can get the total price per ticket.

But can i do it from a formula itself?

Please any suggestions

 

[ShuStar]

Have you tried a sumif? Send the dataset if you can

 

[CA_Punit]

I have Questions

1, Is Promo1 related to Product11a 1b, 1c. (1 in Promo code related to 1 in Product) is this true

 

[Limit Braker]

I have Questions

1, Is Promo1 related to Product11a 1b, 1c. (1 in Promo code related to 1 in Product) is this true

yes precisely, i forgot to mention it. Product1a, 1b, 1c are the products that make promo1 (All possible combinations withouth repetition) and so on.

 

[CA_Punit]

if you dont mind please use xl2bb addin available in the right hand side of the reply box to paste the data so that we dont have to type the data

Download xl2bb----- unzip the file----- Double click and you will find the Mr Excel tab in Excel. Just selct the range and capture range and Generate output. Paste in Reply box

 

[CA_Punit]

How many promo codes can be there??? Max No Promo100 or etc...

 

[CA_Punit]

Try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
2	Ticket	Store	Product	Units	Total
3	1	Store1	Product1a	1	30				Stores	Promo1	Promo2	Promo3
4	1	Store1	Product1b	1	100				Store1	3	0	0
5	1	Store1	Product1c	1	10				Store2	0	2	0
6	2	Store2	Product2a	1	10				Store3	0	0	5
7	2	Store2	Product2b	1	15				Store4	0	2	0
8	3	Store3	Product3a	1	100
9	3	Store3	Product3b	1	15
10	3	Store3	Product3c	1	20
11	3	Store3	Product3d	1	35
12	3	Store3	Product3e	1	12
13	4	Store4	Product2a	1	10
14	4	Store4	Product2b	1	15
15
16
17
18
Sheet9

Cell Formulas
Range	Formula
J4:L7	J4	=SUM(IFERROR(IF($B$3:$B$14=$I4,IF(SEARCH(MID(J$3,6,9999),$C$3:$C$14),$D$3:$D$14)),0))
B6:B14	B6	="Store"&A6

 

[Limit Braker]

Have you tried a sumif? Send the dataset if you can

if you dont mind please use xl2bb addin available in the right hand side of the reply box to paste the data so that we dont have to type the data

Download xl2bb----- unzip the file----- Double click and you will find the Mr Excel tab in Excel. Just selct the range and capture range and Generate output. Paste in Reply box

Thank you everyone for your replies, here is the actual file.

Sheet1) the dataset i use
sheet2) the table where i want to summarize the promotions (Since like i said theresno mix and match code in our ERP yet)
sheet 3) Skus for each promo with all possible combinations without repetition.


Here is the download link for the ones who can help me pls

Mr Excel case.xlsx

 

[CA_Punit]

You have error in the Sheet 3. CREATINE MICRONIZED 300 G - DYMATIZE is available in both Goliath 12lb + Creatine 300g & Glutamine 300g + Creatine 300g

 

[CA_Punit]

With Error Please find the Details

Mr Excel case.xlsx

Microsoft Excel Workbook

1drv.ms