Count data with three criteria: unique values, a yes value and a date

[5inco]

Hi!

I have 3 columns:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]PRODUCTS[/TD]
[TD]SOLD[/TD]
[TD]DATE[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Y[/TD]
[TD]9/24/2013[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]N[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]N[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Y[/TD]
[TD]8/28/2013[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD]Y[/TD]
[TD]9/24/2013[/TD]
[/TR]
[TR]
[TD]Strawberry[/TD]
[TD]Y[/TD]
[TD]9/24/2013[/TD]
[/TR]
[TR]
[TD]Cherry[/TD]
[TD]N[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cherry[/TD]
[TD]Y[/TD]
[TD]9/24/2013[/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD]N[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD]N[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD]N[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need Excel to count only the products who appear once, that have been sold on the date that I write on cell D2.

So if I write on cell D2 the date 9/24/2013, I should get that only 1 product matches these criteria.

I'm using these formula:

={SUMPRODUCT((IFERROR(1/COUNTIF(products;products);0)*1*(sold = "Y")*1*(date=D2)*1);0)}

But it throws a 2, when there's only one product that matches these criteria.

- Of course, it's a matrix formula
- products, sold and date are the defined names for columns A, B and C
- Maybe I don't need a matrix formula?? I don't know...

Thanks a lot for your time and expertise!!

 

[joemeshuggah]

not sure i totally understand, but if i put a unique list in column F of all of your products (starting in F2), and enter this formula in column G I get a count based on your criterea...

=COUNTIFS(A:A,F2,B:B,B2,C:C,D$2)

 

[Hercules1946]

Is your version of Excel 2007 or later?
I don't understand either. Your saying that only one product should match, but in your list you have 4 products sold all meeting the date criteria ?
Am I missing something?

 

[5inco]

Thanks for your interest, but what I'm looking for is only the products which have been sold on 9/24/2013. That would be: apples, pears, strawberries and cherries.

And that have are unique: only strawberry is unique. The others are duplicates.

So if I apply your formula, the result is 1, but if I change strawberry and put pear, it keeps showing 1. So, it's not working. Besides I know this can be done without an auxiliary column, but I don't know how

I appreciate your good will!!

not sure i totally understand, but if i put a unique list in column F of all of your products (starting in F2), and enter this formula in column G I get a count based on your criterea...

=COUNTIFS(A:A,F2,B:B,B2,C:C,D$2)

 

[Yahya]

i am not sure if this is what you are looking for

Excel 2012
	A	B	C	D	E	F
1	PRODUCTS	SOLD	DATE	28/08/2013
2	Apple	Y	24/09/2013			1
3	Apple	N
4	Orange	N
5	Orange	Y	28/08/2013
6	Pear	Y	24/09/2013
7	Strawberry	Y	24/09/2013
8	Cherry	N
9	Cherry	Y	24/09/2013
10	Grapes	N
11	Grapes	N
12	Pear	N
Sheet2

Cell Formulas
Range	Formula
F2	{=SUM(IF(FREQUENCY(IF($B$2:$B$12="Y",IF($C$2:$C$12=$D$1,MATCH($A$2:$A$12,$A$2:$A$12,0))),ROW($A$2:$A$12)-ROW($A$2)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[5inco]

Thanks for asking Hercules1946!

4 products meet the criteria of the date, but not of being unique. Three of them have duplicates: pear, apple and cherry. The only one that is unique is strwberry. That's why it should show 1.

Thanks for your time

Is your version of Excel 2007 or later?
I don't understand either. Your saying that only one product should match, but in your list you have 4 products sold all meeting the date criteria ?
Am I missing something?

 

[5inco]

Hi, Yahya

I don't know why, but though it's the type of solution I'm looking for and it throws 1, which is correct. If I change strawberry for cherry and apply the formula, it keeps counting 1, when it should count 0.

Is the same problem I have with my formula.

Thanks for your effort. I really appreciate it.

i am not sure if this is what you are looking for
Excel 2012

	A	B	C	D	E	F
PRODUCTS	SOLD	DATE
Apple	Y
Apple	N
Orange	N
Orange	Y
Pear	Y
Strawberry	Y
Cherry	N
Cherry	Y
Grapes	N
Grapes	N
Pear	N

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]28/08/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]24/09/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]28/08/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]24/09/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]24/09/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]24/09/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]{=SUM(IF(FREQUENCY(IF($B$2:$B$12="Y",IF($C$2:$C$12=$D$1,MATCH($A$2:$A$12,$A$2:$A$12,0))),ROW($A$2:$A$12)-ROW($A$2)+1),1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Yahya]

you are very welcome
but my formula Working with Date in cell D1

My Counting Depends on
1. Date in CEll D1
2. number of items without duplicate in Column A
3. world "Y" in column B

so if i type date 24/9/2103 on D1 and i changed some product name, it is counting, look at this Example my friend

Excel 2012
	A	B	C	D	E	F
1	PRODUCTS	SOLD	DATE	24/09/2013
2	Apple	Y	24/09/2013			3
3	Apple	N
4	Orange	N
5	Pear	Y	28/08/2013
6	Pear	Y	24/09/2013
7	Pear	Y	24/09/2013
8	Cherry	N
9	Cherry	Y	24/09/2013
10	Grapes	N
11	Grapes	N
12	Pear	N
Sheet2

Cell Formulas
Range	Formula
F2	{=SUM(IF(FREQUENCY(IF($B$2:$B$12="Y",IF($C$2:$C$12=$D$1,MATCH($A$2:$A$12,$A$2:$A$12,0))),ROW($A$2:$A$12)-ROW($A$2)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[5inco]

That's the problem. 3 is not correct because if pear appears 3 times because you've changed strawberry it should show 0, because there are no unique values on the list.

If a products appears twice, then it doesn't match the first criteria. Am I being clear or do you need more details.

Thank you so much!

you are very welcome
but my formula Working with Date in cell D1

My Counting Depends on
1. Date in CEll D1
2. number of items without duplicate in Column A
3. world "Y" in column B

so if i type date 24/9/2103 on D1 and i changed some product name, it is counting, look at this Example my friend

Excel 2012
	A	B	C	D	E	F
1	PRODUCTS	SOLD	DATE	24/09/2013
2	Apple	Y	24/09/2013			3
3	Apple	N
4	Orange	N
5	Pear	Y	28/08/2013
6	Pear	Y	24/09/2013
7	Pear	Y	24/09/2013
8	Cherry	N
9	Cherry	Y	24/09/2013
10	Grapes	N
11	Grapes	N
12	Pear	N
Sheet2

Cell Formulas
Range	Formula
F2	{=SUM(IF(FREQUENCY(IF($B$2:$B$12="Y",IF($C$2:$C$12=$D$1,MATCH($A$2:$A$12,$A$2:$A$12,0))),ROW($A$2:$A$12)-ROW($A$2)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Yahya]

That's the problem. 3 is not correct because if pear appears 3 times because you've changed strawberry it should show 0, because there are no unique values on the list.

If a products appears twice, then it doesn't match the first criteria. Am I being clear or do you need more details.

Thank you so much!

sorry it is not clear for me
in this case what is the Rsult should be ?