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

 

[Jonmo1]

Adding an or type of criteria to countifs goes something like

=SUM(COUNTIFS(Range1,Criteria,Range2,{"apples","oranges"}))

That would be the equivalent of doing
COUNTIFS(Range1,Criteria,Range2,"apples")+COUNTIFS(Range1,Criteria,Range2,"oranges")

Hope that helps.

 

[orlandotuga]

formula must include:
apples and oranges qtd sold must be between min and max values in table 2
also can only count apples and oranges are sold together, in the same day

those formulas also include those criteria ?

 

[Jonmo1]

That was really just a general syntax suggestion, not really specific.

You said

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.

So I was just showing how to take one criteria of a easy countifs function, and make it test for apples or oranges.
Can you post that formula, then we can add in criteria to test for either apples or oranges.

 

[orlandotuga]

the formulas
=(COUNTIFS(F4:F17;"apple";G4:G17;"<"&L4;C4:C17;2015))+(COUNTIFS(F4:F17;"apple";G4:G17;">"&K4;C4:C17;2015))
=(COUNTIFS(F4:F17;"orange";G4:G17;"<"&L4;C4:C17;2015))+(COUNTIFS(F4:F17;"orange";G4:G17;">"&K4;C4:C17;2015))

i want to merge them, but the final formula must consider apples+oranges sold both in same day.

 

[Jonmo1]

Those formulas don't make a lot of sense.
Why they are adding the result of 2 different countifs, one doing > <l4 doing="" other="" the="" and="">K4 and the other doing < L4?
Is that supposed to be the 'between min and max' criteria?

I would think that should be
=COUNTIFS(F4:F17;"apple";G4:G17;"<"&L4;G4:G17;">"&K4;C4:C17;2015)

Now to make that count apples or oranges
=SUM(COUNTIFS(F4:F17;{"apple";"orange"};G4:G17;"<"&L4;G4:G17;">"&K4;C4:C17;2015))


Now, how do we tell if they're on the same day? Which Day?
Is there another column that tells us which day they were sold?</l4>

 

[orlandotuga]

I would think that should be
=COUNTIFS(F4:F17;"apple";G4:G17;"<"&L4;G4:G17;">"&K4;C4:C17;2015)

Yes, that was suposed to be the formula, the one i used does not work correctly. sorry.

Now to make that count apples or oranges
=SUM(COUNTIFS(F4:F17;{"apple";"orange"};G4:G17;"<"&L4;G4:G17;">"&K4;C4:C17;2015))

oranges must be >K5 and <L5 (they are different than apples)

also there is a column with date E4:E17 (dd-mm-yyyy) and yes - i i want to count only if they are both sold in same day
ex: 01-01-2015 have 1 line (apples sold only) and should not count in the formula.
02-05-2015 have 2 lines in table (1 for apples sold and another for oranges sold) and should be counted in the formula

is it possible ?

 

[orlandotuga]

forgot to write in previous post:

*oranges must be >K5 and <L5.

 

[Jonmo1]

Put spaces around < and > symbols, forum software tends to treat them as html code and stuff gets cut off.

 

[orlandotuga]

02-05-2015 have 2 lines in table (1 for apples sold and another for oranges sold) and should be counted in the formula

but count as 1... 1 day which all criteria is met.

i hope im explaining it good.