Display and count unique values in date range

[alfik1]

Hi all,

Im standing in front of the task (see picture attached) and I cannot to figure it out . Does anybody has idea how to achieve desired result based on the criterias defined?
The result should be done by fromula, not by pivot or vba. If possible, do not use any formula in data table...
Im using excel 2016
Many thanx for any idea

 

[PhilS2520]

Hi alfik1,

Look into the SUMIF() function. Here is some information:

https://www.techonthenet.com/excel/formulas/sumif.php

 

[Marcelo Branco]

Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td]

Data​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

Results​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td][/td][td]

A​

[/td][td]

B​

[/td][td][/td][td]

From​

[/td][td]

01/02/2018​

[/td][td][/td][td]

A​

[/td][td]

B​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td][/td][td]

111​

[/td][td]

01/02/2018​

[/td][td][/td][td]

Till​

[/td][td]

06/02/2018​

[/td][td][/td][td]

111​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td][/td][td]

222​

[/td][td]

02/02/2018​

[/td][td][/td][td][/td][td][/td][td][/td][td]

222​

[/td][td]

4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td]

222​

[/td][td]

03/02/2018​

[/td][td][/td][td][/td][td][/td][td][/td][td]

-​

[/td][td]

-​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td]

222​

[/td][td]

04/02/2018​

[/td][td][/td][td][/td][td][/td][td][/td][td]

-​

[/td][td]

-​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td]

222​

[/td][td]

05/02/2018​

[/td][td][/td][td][/td][td][/td][td][/td][td]

-​

[/td][td]

-​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td]

111​

[/td][td]

06/02/2018​

[/td][td][/td][td][/td][td][/td][td][/td][td]

-​

[/td][td]

-​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td]

111​

[/td][td]

07/02/2018​

[/td][td][/td][td][/td][td][/td][td][/td][td]

-​

[/td][td]

-​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td][/td][td]

333​

[/td][td]

08/02/2018​

[/td][td][/td][td][/td][td][/td][td][/td][td]

-​

[/td][td]

-​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td][/td][td]

222​

[/td][td]

09/02/2018​

[/td][td][/td][td][/td][td][/td][td][/td][td]

-​

[/td][td]

-​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td][/td][td]

222​

[/td][td]

10/02/2018​

[/td][td][/td][td][/td][td][/td][td][/td][td]

-​

[/td][td]

-​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Array formula in H3 copied down
=IFERROR(INDEX(Data[A],SMALL(IF(FREQUENCY(IF(Data>=$F$2,IF(Data<=$F$3,MATCH(Data[A],Data[A],0))),ROW(Data[A])-MIN(ROW(Data[A]))+1),ROW(Data[A])-MIN(ROW(Data[A]))+1),ROWS(H$3:H3))),"-")
confirmed with Ctrl+Shift+Enter, not just Enter

Formula I3 copied down
=IF(H3="-","-",COUNTIFS(Data[A],H3,Data,">="&$F$2,Data,"<="&$F$3))

Hope this helps

M.

 

[alfik1]

Hi Phil,

I think function SUMIF() wont be enough, because I dont know the criterium (exact value) needed for this calculation.
I need to implement also LARGE function probably.
Also some other functions to have required result

 

[Marcelo Branco]

Hi Phil,

I think function SUMIF() wont be enough, because I dont know the criterium (exact value) needed for this calculation.
I need to implement also LARGE function probably.
Also some other functions to have required result

Have you tried the formulas in post 3?

M.

 

[alfik1]

Have you tried the formulas in post 3?

M.

Hi Marcelo, yes I already tried this formula.
On the picture you can see the result.
I substituted SMALL function with LARGE to have top 10 list, but it is sorting on base of values in the cells of data A. I would like to sort them on base how many times appears - see desired result in tle picture. Is it possible?

 

[Marcelo Branco]

Are the codes in column A of table Data numeric?

M.

 

[alfik1]

Are the codes in column A of table Data numeric?

M.

Hi Marcelo,
yes values are numbers. Is it a problem?

 

[Marcelo Branco]

No, but to put in descending order the top 10 requires a very complicated formula.
A helper column would be acceptable?

M.

 

[alfik1]

No, but to put in descending order the top 10 requires a very complicated formula.
A helper column would be acceptable?

M.

OK, try with helper column.