Countifs with multiple criteria

[orlandotuga]

Hi.

This is my first time in this forum.

I have a table in this link https://filehost.net/234a1151ff7695d3
and i cant figure a formula that works

The criteria are inside file, but i need a formula that counts products sold in each day, by year, between a min and max value. this is the easy part and i can make with a countifs formula.

BUT, in this example my products are apples and oranges and both need to meet those criteria but also must be sold in same day.

Which means apples+orange sold together in each day (does not count sold alone in a day), by year, between a min and max value.

If it looks confusing, maybe linked excel table will show what i mean. i used colors to represent which lines will be counted for each criteria.

I hope someone can help me, if some formula is possible LOL

Thank you

 

[orlandotuga]

oranges must be > K5 and < L5

 

[Jonmo1]

Ok, so you really want to count how many DAYS within the year/store that sold both oranges and apples between a min and max.
Complicating it even more is there is a different min/max criteria for apples vs oranges.

This is going far beyond any simple adjustment to a standard countifs, and may take some time to figure out.
It may require adding columns to your layout.

Anyone else have any input?

 

[orlandotuga]

Ok, so you really want to count how many DAYS within the year/store that sold both oranges and apples between a min and max.
Complicating it even more is there is a different min/max criteria for apples vs oranges.

Yes, thats exactly what i want.


Explaining that was hard for me (my problem was finding the right words in the logic order to everything make sense)

Formula might be harder than me explaining, if even possible LOL.

I hope you (or someone else) can help me

I will wait. thanks

 

[Jonmo1]

Here's the best I can come up with so far, with lots of helper columns and cells

Book1
	C	D	E	F	G	H	I	J	K	L
2	sales
3	year	store	data	product	qtd	Tolerance		limit	min	max
4	2015	A	12/15/2015	apple	2	TRUE		apple	1	6
5	2015	A	1/1/2015	orange	3	TRUE		orange	3	5
6	2015	A	10/13/2015	apple	5	TRUE
7	2015	A	10/13/2015	orange	7	FALSE
8	2015	A	10/15/2015	apple	3	TRUE
9	2015	A	10/15/2015	orange	4	TRUE
10	2015	B	10/15/2015	apple	3	TRUE
11	2015	B	10/15/2015	orange	4	TRUE
12	2016	A	1/15/2016	apple	9	FALSE
13	2016	A	1/15/2016	orange	2	FALSE
14	2016	B	1/15/2016	apple	4	TRUE
15	2016	B	1/15/2016	orange	4	TRUE
16	2016	B	1/15/2016	apple	4	TRUE
17	2016	B	1/15/2016	orange	5	TRUE
18
19
20	Store	data	2015	2016
21	A	12/15/2015	0	0
22	A	1/1/2015	0	0
23	A	10/13/2015	0	0
24	A	10/15/2015	1	0
25	A	1/15/2016	0	0
26	B	12/15/2015	0	0
27	B	1/1/2015	0	0
28	B	10/13/2015	0	0
29	B	10/15/2015	1	0
30	B	1/15/2016	0	1
31
32	Total		2	1
Folha1

Cell Formulas
Range	Formula
H4	=AND(G4>=VLOOKUP(F4,$J$4:$L$5,2,0),G4<=VLOOKUP(F4,$J$4:$L$5,3,0))
E21	=--AND(COUNTIFS($C$4:$C$17,E$20,$D$4:$D$17,$C21,$E$4:$E$17,$D21,$F$4:$F$17,"apple",$H$4:$H$17,TRUE),COUNTIFS($C$4:$C$17,E$20,$D$4:$D$17,$C21,$E$4:$E$17,$D21,$F$4:$F$17,"orange",$H$4:$H$17,TRUE))
E32	=AGGREGATE(9,3,E21:E30)
F21	=--AND(COUNTIFS($C$4:$C$17,F$20,$D$4:$D$17,$C21,$E$4:$E$17,$D21,$F$4:$F$17,"apple",$H$4:$H$17,TRUE),COUNTIFS($C$4:$C$17,F$20,$D$4:$D$17,$C21,$E$4:$E$17,$D21,$F$4:$F$17,"orange",$H$4:$H$17,TRUE))
F32	=AGGREGATE(9,3,F21:F30)

 

[Jonmo1]

First I added column H to test for the tolerances based on apple or orange
=AND(G4>=VLOOKUP(F4,$J$4:$L$5,2,0),G4<=VLOOKUP(F4,$J$4:$L$5,3,0))

Then created a unique list of the dates in D21:D25 - repeated in D26:D30
Then each store name represented in C21:C30

Formula in E21 filled down and right is
=--AND(COUNTIFS($C$4:$C$17,E$20,$D$4:$D$17,$C21,$E$4:$E$17,$D21,$F$4:$F$17,"apple",$H$4:$H$17,TRUE),COUNTIFS($C$4:$C$17,E$20,$D$4:$D$17,$C21,$E$4:$E$17,$D21,$F$4:$F$17,"orange",$H$4:$H$17,TRUE))

This gives a 1 or 0, 1 if the given date/store sold both apple and orange within the specific min/max tolerance.

Then you can sum those 1's and 0's to make a count
=AGGREGATE(9,3,E21:E30)
I used aggregate in E32, so it can ignore hidden rows. Therefor you can filter column C by Store.

 

[orlandotuga]

Hi.
Sorry, I couldnt reply sooner.

I've read everything and I'll replicate your tables/formulas in my excel. It would be great if it could be done without the second table, but at least now I have a working solution to count automatically the days in the excel.

Thank you so much for the help