Count Unique Dates, not knowing dates

[karen53]

Hi,

I need to count the number of unique dates in a column where the value in the next column is "A." I don't want to count repeated dates. Here is what I have so far.

=SUMPRODUCT(--('LA and LAN'!$BV$5:$BV$2100>=MIN('LA and LAN'!$BV$5:$BV$2100)),--('LA and LAN'!$BV$5:$BV$2100<=MAX('LA and LAN'!$BV$5:$BV$2100))--('LA and LAN'!$BW$5:$BW$2100="A"))

Is there a way to count the number of unique dates rather than the number of dates between two dates?

Thanks

[rylo]

Hi

You will have to adjust your ranges, but try

=SUM(IF(FREQUENCY(IF(H3:H10="A",G3:G10,""),IF(H3:H10="A",G3:G10,""))>0,1)) (array entered)

HTH

rylo

[Teethless mama]

Hi

You will have to adjust your ranges, but try

=SUM(IF(FREQUENCY(IF(H3:H10="A",G3:G10,""),IF(H3:H10="A",G3:G10,""))>0,1)) (array entered)

HTH

rylo

Your formula can reduce to this:

=SUM(SIGN(FREQUENCY(IF(H3:H10="A",G3:G10),G3:G10)))

Ctrl+Shift+Enter, not just Enter

[karen53]

Wow!!! That worked great!!! Thank you both for your responses!

Toothless mama, I would like to understand your formula. I get everything but sign. I tried looking it up in office help but am only finding plus signs, etc. How would I look sign up so I have what you used?

Also, I am new, how do I mark this thread solved?

[rylo]

Hi

Try using the excel help file. This is what came up for me.

SIGN function
Show AllHide All
Determines the sign of a number. Returns 1 if the number is positive, zero (0) if the number is 0, and -1 if the number is negative.

Syntax

SIGN(number)

Number is any real number.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example
Create a blank workbook or worksheet.Select the example in the Help topic. Note Do not select the row or column headers.

Selecting an example from HelpPress CTRL+C.In the worksheet, select cell A1, and press CTRL+V.To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

1
2
3
4
A B
Formula Description (Result)
=SIGN(10) Sign of a positive number (1)
=SIGN(4-4) Sign of zero (0)
=SIGN(-0.00001) Sign of a negative number (-1)


On the SOLVED issue, go to the FAQ. There are instructions on how to edit the original post.

HTH

rylo