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!!

 

[5inco]

Hi!

In your example, it should be 0, because if you write pear, instead of strawberry, then no product matches the first criteria: there's only one product. In my example, Strawberry only shows once and matches the second criteria: the date. Pear shows more than once, so it doesn't match first criteria.

That's why if strawberry shows only once and matches the date, the result should be 1. If we add for example lemon and it only shows once in the table and matches the date, then result must be 2.

Hope this helps. Thank you very much.

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

 

[Hercules1946]

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

I think I understand now. If a fruit name is listed in your table, it should only be counted as unique when it first arrives.
So you want to count fruits being sold for the first time (eg today, if that was the date specified)
As a matter of interest, why do the duplicates appear on other dates with sales = N ? If you haven't sold any why do they count?

 

[5inco]

Hi!

That's it!. You got it. The N is because more product came in and is on stock, but hasn't been sold yet. I know it's not logical, but my boss wants it like this.

Thanks for your help

I think I understand now. If a fruit name is listed in your table, it should only be counted as unique when it first arrives.
So you want to count fruits being sold for the first time (eg today, if that was the date specified)
As a matter of interest, why do the duplicates appear on other dates with sales = N ? If you haven't sold any why do they count?

 

[Hercules1946]

Hello Sinco
I managed to get a formula that works, but I had to add an extra "helper" column (in A) that calculates how many times each fruit name occurs in Col B.
If you copy the formula in A down a large block, this will allow for expansion in your table. You can even hide the column if you don't want to see it, or relocate it,
but if you do the relocate you will need to edit the formula in F1. When you enter the F1 formula, or make any amendments to it, you will need to set the array
formula status before leaving enter mode or edit mode. Do this by pressing COntrol+Shift+Enter. If All is well, Excel will add braces {} at the beginning and end of the formula.
You can't do this by typing in the braces

Here is a screen shot (hopefully) showing you where the formulas go:

Excel 2010
	A	B	C	D	E	F
1	Product Count	Product	Sold	Date Sold	Date Tested	First Sales
2	2	Apple	Y	9/24/2013	9/24/2013	1
3	2	Apple	N
4	2	Orange	N
5	2	Orange	Y	8/28/2013
6	2	Pear	Y	9/24/2013
7	1	Strawberry	Y	9/24/2013
8	2	Cherry	N
9	2	Cherry	Y	9/24/2013
10	2	Grapes	N
11	2	Grapes	N
12	2	Pear	N
Sheet1

Cell Formulas
Range	Formula
A2	=IF(B2= "","",COUNTIF($B$2:$B$1000,B2))
A3	=IF(B3= "","",COUNTIF($B$2:$B$1000,B3))
A4	=IF(B4= "","",COUNTIF($B$2:$B$1000,B4))
A5	=IF(B5= "","",COUNTIF($B$2:$B$1000,B5))
A6	=IF(B6= "","",COUNTIF($B$2:$B$1000,B6))
A7	=IF(B7= "","",COUNTIF($B$2:$B$1000,B7))
A8	=IF(B8= "","",COUNTIF($B$2:$B$1000,B8))
A9	=IF(B9= "","",COUNTIF($B$2:$B$1000,B9))
A10	=IF(B10= "","",COUNTIF($B$2:$B$1000,B10))
A11	=IF(B11= "","",COUNTIF($B$2:$B$1000,B11))
A12	=IF(B12= "","",COUNTIF($B$2:$B$1000,B12))
F2	{=SUM(IF(FREQUENCY(IF($C$2:$C$12="Y",IF($D$2:$D$12=$E$2,IF($A$2:$A$12= 1,MATCH($B$2:$B$12,$B$2:$B$12,0)))),ROW($B$2:$B$12)-ROW($B$2)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Hercules1946]

Sorry- Forgot something. It would probably be a good idea to extend the arrays for Cols B,C, and D as the rows only run down to 12. This will save editing it if you
extend the table:

I suggest:

=SUM(IF(FREQUENCY(IF($C$2:$C$1000="Y",IF($D$2:$D$1000=$E$2,IF($A$2:$A$1000= 1,MATCH($B$2:$B$1000,$B$2:$B$1000,0)))),ROW($B$2:$B$1000)-ROW($B$2)+1),1))

In cell F1.

Don't forget Ctrl+Shift+Enter

Good Luck
Hercules

 

[5inco]

Hi, Hercules!

Thank you very much for your time, but I need a formula that makes the calculation in one step. I think that I've found it:

=SUMPRODUCT((COUNTIF(A2:A12;A2:A12&"")=1)*(B2:B12="Y")*(C2:C12=D1))

- It's faster than a matrix formula
- It's shorter
- It doesn't show fractions

Thanks for your time to you and all of you have tried to help me!!

Sorry- Forgot something. It would probably be a good idea to extend the arrays for Cols B,C, and D as the rows only run down to 12. This will save editing it if you
extend the table:

I suggest:

=SUM(IF(FREQUENCY(IF($C$2:$C$1000="Y",IF($D$2:$D$1000=$E$2,IF($A$2:$A$1000= 1,MATCH($B$2:$B$1000,$B$2:$B$1000,0)))),ROW($B$2:$B$1000)-ROW($B$2)+1),1))

In cell F1.

Don't forget Ctrl+Shift+Enter

Good Luck
Hercules

 

[Hercules1946]

Hi, Hercules!

Thank you very much for your time, but I need a formula that makes the calculation in one step. I think that I've found it:

=SUMPRODUCT((COUNTIF(A2:A12;A2:A12&"")=1)*(B2:B12="Y")*(C2:C12=D1))

- It's faster than a matrix formula
- It's shorter
- It doesn't show fractions

Thanks for your time to you and all of you have tried to help me!!

Hi Sinco
Im glad you found the right solution. You have probably picked it up, but your formula needs a slight adjustment otherwise you get an error:

=SUMPRODUCT((COUNTIF(A2:A12,A2:A12&"")=1)*(B2:B12="Y")*(C2:C12=D1))

best wishes
Hercules