need a count of unique dates in same row and specific date range

[heidi_meister]

is there a way to use:
"find/count unique values" =SUMPRODUCT((K6:AR6<>"")/COUNTIF(K6:AR6,K6:AR6&""))

in combination with:
"find/count dates in range" =COUNTIFS(K6:AR6,">="&$B$3,K6:AR6,"<="&$C$3)

B3 = start date
C3 = end date

For example, my problem is the unique value count (depending on the row) is 10 and the dates in the range is 8 but I need both of these functions combined to show only the unique dates in the set range which might end up being only 3.

There is also random data in some of the cells.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($K$6:$R$6>=$B$3,IF($K$6:$R$6<=$C$3,$K$6:$R$6)),$K$6:$R$6),1))

 

[heidi_meister]

Hello, I tried this formula and it is not counting everything. For example, my first test row has 8 unique dates within the date range and this formula returned 4.

Could you explain Control+shift+enter as that is new to me? Also, does having to do this present any issues when copying the formula to multiple rows. I have approximately 20,000 rows.

Thank you for your help with this.

 

[Sandie1712]

Hi heidi_meister

Hope this shall resolve your concern

a) =SUMPRODUCT(1/COUNTIFS($J$2:$W$2,$J$2:$W$2))

b) =SUM(1/COUNTIFS($J$2:$W$2,$J$2:$W$2)) and press cntrl+ shift + Enter

 

[Sandie1712]

Hi heidi_meister

Hope this shall resolve your concern

a) =SUMPRODUCT(1/COUNTIFS($J$2:$W$2,$J$2:$W$2))

b) =SUM(1/COUNTIFS($J$2:$W$2,$J$2:$W$2)) and press cntrl+ shift + Enter

One addition in case your start date and end date is in different cell pl use the below mentioned

a) =SUMPRODUCT(($J$4:$W$4>=$K$2)*($J$4:$W$4<=$U$2)*(1/COUNTIFS($J$4:$W$4,$J$4:$W$4)))

b) =SUM(($J$4:$W$4>=$K$2)*($J$4:$W$4<=$U$2)*(1/COUNTIFS($J$4:$W$4,$J$4:$W$4))) and press cntrl+ shift + Enter

 

[Sandie1712]

it"s Awesome, i was restricted for frequency with numbers only!!

Great Learning for me

Thank You

 

[Aladin Akyurek]

Hello, I tried this formula and it is not counting everything. For example, my first test row has 8 unique dates within the date range and this formula returned 4.

Could you explain Control+shift+enter as that is new to me? Also, does having to do this present any issues when copying the formula to multiple rows. I have approximately 20,000 rows.

Thank you for your help with this.

How did you implement the formula? Control+shift+enter means: Press down the control and the shift keys at the same while you hit the enter key. If succeeded correctly, Excel itselft puts a pair of { and } around the formula in recognition.

By the way, this formula is more efficient that other alternatives. See: Performance of Formulas for Unique Count

 

[heidi_meister]

Hi Aladin, that is exactly how I implemented the formula and it did put a pair of { and } around the formula in recognition.

Any ideas why it is not counting every date it should?

 

[heidi_meister]

Hi Sandie, I tried these two formulas that you suggested

a) =SUMPRODUCT(($J$4:$W$4>=$K$2)*($J$4:$W$4<=$U$2)*(1/COUNTIFS($J$4:$W$4,$J$4:$W$4)))

b) =SUM(($J$4:$W$4>=$K$2)*($J$4:$W$4<=$U$2)*(1/COUNTIFS($J$4:$W$4,$J$4:$W$4))) and press cntrl+ shift + Enter

Both returned #DIV/0! -----do you think this because many of my cells are blank?

 

[Aladin Akyurek]

Hi Aladin, that is exactly how I implemented the formula and it did put a pair of { and } around the formula in recognition.

Any ideas why it is not counting every date it should?

Are your dates true dates? Lets test that: What do the following formulas return?

1.

=SUMPRODUCT(--($K$6:$R$6>=$B$3),--($K$6:$R$6<=$C$3))

assuming that B3 and C3 are true dates.

2.

=SUMPRODUCT(ISNUMBER($K$6:$R$6)+0)