CODEWITHSUNDEEP
excelexcel-formula
AxleWack
AxleWack

Reputation: 1911

Counting number of unique days that a certain value falls in using Excel FOrmula

I have the following Data:

enter image description here

I have tried many formulas, that keep returning #Div/0 or #Value, but I have gotten to a formula that I think can work, but I dont know how to adapt it, I am very new to formulas:

=SUM(IF(FREQUENCY(MATCH(C1,B2:B10,0),MATCH(C1,B2:B10,0))>0,1))

By using Ctrl + Shift + Enter, it creates an array as far as I understand and in this case returns 1, which is correct in this case, as there is only 1 unique value for "H72"

If I change the formula to:

=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))

and by pushing Ctrl+Shift+Enter, I get a value of 3, which is also correct, as there are 3 unique values in the list(H72, H7, T122).

However, what I want to do is get the number of Unique Dates from Column A where Column B = cell C1(In this case H72), but I have no idea how to adapt this formula. So in other words, the value that must be returned in this case is 3, as H72 falls within 3 different dates. However, if there were duplicates :

Date | Data

2017-12-01 | H72

2017-12-01 | H72

2017-12-02 | H72

The total should only be 2, as H72 falls under 2 unique dates

-----UPDATE------

So I tried another formula, making use of SUMPRODUCT and COUNTIF and it gives the result I am looking for, except for if there are duplicate dates, then instead of showing 3, it shows 2.5:

enter image description here

=SUMPRODUCT(((A2:A10 <> "")*(B2:B10=C1))/COUNTIF(A2:A10,A2:A10 & ""))

Please could someone help ?

Thanks in advance for the help.

Upvotes: 1

Views: 212

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34180

Because dates are numeric values, you shouldn't need to use match

=SUM(IF(FREQUENCY(IF(B2:B10=C1,A2:A10),A2:A10)>0,1))

see support article

Also this formula does ignore blank values.

As before, needs to be entered with CtrlShiftEnter

Upvotes: 3

Alex P
Alex P

Reputation: 12489

Try this:

=SUM(IF(FREQUENCY(IF(B2:B10=C1,IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0))),ROW(A2:A10)-ROW(A2)+1),1))

Notes:

  • C1 is your look up value e.g. H72
  • Formula must be entered as an array formula {CTRL + SHIFT + ENTER}

Upvotes: 1

Related Questions