Count number of non sequential number of days (1 Viewer)

machumpion · Jun 6, 2016

Let's say I have a column A:

May 1
May 2
May 5
May 6
May 6
May 9

The cells are formatted as dates. How can I return the count of the number of days in this range? i.e. 5

Thanks!

arnelgp · Jun 7, 2016

is this in access?
if it is you can use a query:

SELECT Count(*) FROM (SELECT DISTINCT [dateField] FROM yourTable);

machumpion · Jun 7, 2016

arnelgp said:
is this in access?
if it is you can use a query:

SELECT Count(*) FROM (SELECT DISTINCT [dateField] FROM yourTable);

THis is excel actually, is there a way to do it without a pivot table?

arnelgp · Jun 7, 2016

=SUMPRODUCT(1/COUNTIF(range1, range1))

so if your date is in column A rows 1-5
=SUMPRODUCT(1/COUNTIF(A1:A5, A1:A5))

source: https://exceljet.net/formula/count-unique-values-in-a-range-with-countif

stopher · Jun 7, 2016

Here's another alternative to arnelgp's perfectly good answer

=SUM(IF(FREQUENCY(A1:A5,A1:A5)>0,1))

Source: https://support.office.com/en-gb/article/Count-unique-values-among-duplicates-7889942d-824e-4469-893c-191d1efde950

machumpion · Jun 7, 2016

this website keeps me employed, thanks!

machumpion · Jun 7, 2016

i have a followup question,

how would I only count the number of days that proceed a a given date (i.e. the days in Column A) for up to 4 prior days?

For example, for May 9 from my initial post, return 2 (May 5, May 6).

Thanks in advance.

arnelgp · Jun 7, 2016

same formula, i guess:

=SUMPRODUCT(1/COUNTIF(range1, range1)) - (NoOfPriorDays - 1)

machumpion · Jun 7, 2016

Hi arnelgp,

That last formula is not quite working for me.

In column A I have a list of non-sequential dates i.e.

May 5
May 5
May 6
May 7
May 8
May 9
May11
May 15

I'd like the formula to check each date in column A, then return the number of unique days listed in Column A that occur before the checked date for up to 4 prior days

So based on the Column A i listed in this post, it should return
0
0
1
2
3
4
3
1

Thanks!

machumpion · Jun 17, 2016

Can someone help me modify the formula proposed in the last solution of this link:
http://stackoverflow.com/questions/27021006/countif-unique-dates-between-a-range

=SUM(IF(FREQUENCY(IF($A$1:$A$97>=D$1;IF($A$1:$A$97<=D$2;IF($A$1:$A$97<>"";MATCH($A$1:$A$97;$A$1:$A$97;0))));ROW($A$1:$A$97)-ROW($A$1)+1);1))

The original formula references a start date and end date (D1 & D2 respectively). I need the start date to be whatever the date is in column A, less 3 days. I need the end date to be the date in column A.
I tried modifying the formula to:

=SUM(IF(FREQUENCY(IF($A$1:$A$8>=A1-3,IF($A$1:$A$8<=A1,IF($A$1:$A$8<>"",MATCH($A$1:$A$8,$A$1:$A$8,0)))),ROW($A$1:$A$8)-ROW($A$1)+1),1))

but it returns 1 for all the dates in column A.

Thanks!

NBVC · Jun 20, 2016

You must confirm the formula with CTRL+SHIFT+ENTER not just ENTER (you should see curly brackets appear around the formula), then copy it down. It is an Array Formula.

machumpion · Jun 20, 2016

NBVC said:
You must confirm the formula with CTRL+SHIFT+ENTER not just ENTER (you should see curly brackets appear around the formula), then copy it down. It is an Array Formula.

Hi NBVC,

I took the formula and used it as an array, but it is not returning the right output for some reason.

I'd like for it to count the date and the number of preceding days for up to 3 preceding days for each respective date in Column A.

I attached a screen capture of my workbook. The formula is the same as in the prior post except it is an array formula.

NBVC · Jun 22, 2016

Your dates start in A1, but you don't include A1 in your formula ranges. Is that how it should be? Try revising the formula to include A1 and let me know if it corrects the problem.

If it doesn't, please attach an excel spreadsheet sample and show the expected results with explanation.

machumpion · Jun 22, 2016

NBVC said:
Your dates start in A1, but you don't include A1 in your formula ranges. Is that how it should be? Try revising the formula to include A1 and let me know if it corrects the problem.

If it doesn't, please attach an excel spreadsheet sample and show the expected results with explanation.

it's working now:banghead:

machumpion · Jun 23, 2016

On further inspection, the formula only seems to count the dates properly up to row 841.

In the workbook attached, notice that on for A841 (Jun 6 2016), it counts 6 days in column A that precede Jun 6, 2016 within 8 days (May 29-Jun 6). Immediately in the next row A842 (Jun 6 2016), it then counts 5 days even though it should be returning one value for each date.

Does anyone know what the problem is?

edit: when you change the sorting on the dates, the values in column B will change too.. im baffled

NBVC · Jun 24, 2016

In your formula you have set the part ROW(all!$A3) with a relative reference, but it should be absolute reference... i.e. ROW(all!$A$3).

Try this revised formula. Remember to confirm with CSE keys before copying down.

=(SUM(IF(FREQUENCY(IF(all!$A$3:$A$1468>=all!$A3-8,IF(all!$A$3:$A$1468<=all!$A3,IF(all!$A$3:$A$1468<>"",MATCH(all!$A$3:$A$1468,all!$A$3:$A$1468,0)))),ROW(all!$A$3:$A$1468)-ROW(all!$A$3)+1),1)))

also, you don't need to self-reference the sheet your formula is in. You can/should take out all the "all!" sheet references unless you are putting the formula in another sheet.

machumpion · Jun 24, 2016

NBVC said:
In your formula you have set the part ROW(all!$A3) with a relative reference, but it should be absolute reference... i.e. ROW(all!$A$3).

Try this revised formula. Remember to confirm with CSE keys before copying down.

=(SUM(IF(FREQUENCY(IF(all!$A$3:$A$1468>=all!$A3-8,IF(all!$A$3:$A$1468<=all!$A3,IF(all!$A$3:$A$1468<>"",MATCH(all!$A$3:$A$1468,all!$A$3:$A$1468,0)))),ROW(all!$A$3:$A$1468)-ROW(all!$A$3)+1),1)))

also, you don't need to self-reference the sheet your formula is in. You can/should take out all the "all!" sheet references unless you are putting the formula in another sheet.

It seems to be working perfectly for all rows now, thanks NBVC. This website has been instrumental to my employment

machumpion · Aug 23, 2016

For some reason, when the column with the dates are past August 14, 2016, the formula returns #div/0 when there are most definitely dates still within 8 days in previous rows.

Why might this be happening? advanced thanks.

NBVC · Aug 26, 2016

I don't see that error in your attachment, but I did notice that your formula has an invalid reference identified with the #REF! error...

=SUMIFS('8 day average'!$B$3:$B$19,'8 day average'!$A$3:$A$19,">="&'8 day average'!$A3-8,'8 day average'!$A$3:$A$19,"<="&'8 day average'!$A3)/(SUM(IF(FREQUENCY(IF('8 day average'!$A$3:$A$19>='8 day average'!$A3-8,IF('8 day average'!$A$3:$A$19<='8 day average'!$A3,IF('8 day average'!$A$3:$A$19<>"",MATCH('8 day average'!$A$3:$A$19,'8 day average'!$A$3:$A$19,0)))),ROW('8 day average'!$A$3:$A$19)-ROW('8 day average'!#REF!)+1),1)))

Try fixing it as:

=SUMIFS('8 day average'!$B$3:$B$19,'8 day average'!$A$3:$A$19,">="&'8 day average'!$A3-8,'8 day average'!$A$3:$A$19,"<="&'8 day average'!$A3)/(SUM(IF(FREQUENCY(IF('8 day average'!$A$3:$A$19>='8 day average'!$A3-8,IF('8 day average'!$A$3:$A$19<='8 day average'!$A3,IF('8 day average'!$A$3:$A$19<>"",MATCH('8 day average'!$A$3:$A$19,'8 day average'!$A$3:$A$19,0)))),ROW('8 day average'!$A$3:$A$19)-ROW('8 day average'!$A$3)+1),1)))

reconfirmed with CTRL+SHIFT+ENTER and copied down and see if that fixes your issue.

Count number of non sequential number of days (1 Viewer)

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

AWF VIP

Registered User.

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

Only trying to help

Registered User.

Attachments

Only trying to help

Registered User.

Registered User.

Attachments

Only trying to help

Registered User.

Registered User.

Attachments

Only trying to help

Similar threads

Users who are viewing this thread