Unique Values and Dates

Question

I have a large spreadsheet. One of the columns (C) contains ID numbers. Another column (G) has dates (mm/dd/yyyy and time portion). I am trying to find unique ID numbers from Column G falling above or below a specific date (10/01/2010). Here is the formula I am using:

=SUM(--(FREQUENCY(IF(G1130:G1181>"10/01/2010",MATCH(C1130:C1181,C1130:C1181,0)),ROW(INDIRECT("1:"&ROWS(C1130:C1181))))>0))

I am not getting any results. Is it the formula or perhaps the time should be incorporated into the date requested in the formula or even a wildcard character to account for the time portion?

Thank you in advance.

Answer 1

I have a large spreadsheet. One of the columns (C) contains ID numbers. Another column (G) has dates (mm/dd/yyyy and time portion). I am trying to find unique ID numbers from Column G falling above or below a specific date (10/01/2010). Here is the formula I am using:

=SUM(--(FREQUENCY(IF(G1130:G1181>"10/01/2010",MATCH(C1130:C1181,C1130:C1181,0)),ROW(INDIRECT("1:"&ROWS(C1130:C1181))))>0))

I am not getting any results. Is it the formula or perhaps the time should be incorporated into the date requested in the formula or even a wildcard character to account for the time portion?

Thank you in advance.

Your date is beng evaluated as the literal TEXT string 10/01/2010.

It will work like this:

=SUM(--(FREQUENCY(IF(G1130:G1181>--"10/01/2010",MATCH(C1130:C1181,C1130:C1181,0)),ROW(INDIRECT("1:"&ROWS(C1130:C1181))))>0))

However, I think you'd be better off using a cell to hold the date:

A1 = 10/1/2010

Then:

=SUM(--(FREQUENCY(IF(G1130:G1181>A1,MATCH(C1130:C1181,C1130:C1181,0)),ROW(INDIRECT("1:"&ROWS(C1130:C1181))))>0))

However (!), I'd suggest using this version:

=SUM(IF(FREQUENCY(IF(G1130:G1181>A1,MATCH(C1130:C1181,C1130:C1181,0)),ROW(C1130:C1181)-ROW(C1130)+1),1))

Overall, it's more efficient and it doesn't use the volatile INDIRECT function.

All formulas need to be array entered.

--

Biff

Microsoft Excel MVP

Answer 2

Thank you. I still must be doing something incorrect. I used:

=SUM(IF(FREQUENCY(IF(G1130:G1182>=N1183,MATCH(C1130:C1182,C1130:C1182,0)),ROW(C1130:C1182)-ROW(C1130)+1),1)) and the correct answer was displayed but when I used:

=SUM(IF(FREQUENCY(IF(G1130:G1182<N1183,MATCH(C1130:C1182,C1130:C1182,0)),ROW(C1130:C1182)-ROW(C1130)+1),1)) I get #N/A.

Never used a formula this involved in Excel. What am I doing incorrectly?

For the "less than" formula, if there are empty cells in C1130:C1182 you'll get that error.

Try this version to account for empty cells (array entered):

=SUM(IF(FREQUENCY(IF(G1130:G1182<N1183,IF(C1130:C1182<>"",MATCH(C1130:C1182,C1130:C1182,0))),ROW(C1130:C1182)-ROW(C1130)+1),1))

--

Biff

Microsoft Excel MVP

Answer 3

Thank you! I figured it out!

Answer 4

Thank you. I still must be doing something incorrect. I used:

=SUM(IF(FREQUENCY(IF(G1130:G1182>=N1183,MATCH(C1130:C1182,C1130:C1182,0)),ROW(C1130:C1182)-ROW(C1130)+1),1)) and the correct answer was displayed but when I used:

=SUM(IF(FREQUENCY(IF(G1130:G1182<N1183,MATCH(C1130:C1182,C1130:C1182,0)),ROW(C1130:C1182)-ROW(C1130)+1),1)) I get #N/A.

Never used a formula this involved in Excel. What am I doing incorrectly?

Answer 5

Hi,

Try it like this ARRAY entered

=SUM(--(FREQUENCY(IF(G1130:G1181>DATE(2010,1,10),MATCH(C1130:C1181,C1130:C1181,0)),ROW(INDIRECT("1:"&ROWS(C1130:C1181))))>0))

but I wouldn't have the date in the formula I'd use a range

=SUM(--(FREQUENCY(IF(G1130:G1181>B1123,MATCH(C1130:C1181,C1130:C1181,0)),ROW(INDIRECT("1:"&ROWS(C1130:C1181))))>0))

---

If this post answers your question, please mark it as the Answer.

Mike H