# Count formula for dates greater than 30 days old with exclusions and not count blanks

1. ## Count formula for dates greater than 30 days old with exclusions and not count blanks

Hello i need a formula to count the number of dates in a column which are 30 days prior, excluding any blanks. I also need it to look at 3 other columns and if there is data in those columns to exculde these from the count as well.

i am trying to count how many letters sent are 30 days old, then would also like to count how many will be thirty days old tomorrow.

letter sent coulumn is N and has a date in the row with a format of 3/22/2012
exclude any rows from the count if there is data in the Row of Column L, T, or X.....

PLEASE HELP, keep getting errors, and dont want to uase a macro as i need to pull this from a shared workbook and multiple tabs
-MATT

2. ## Re: Count formula for dates greater than 30 days old with exclusions and not count blanks

Try using the array below:

=SUM(IF(ISBLANK(N:N),0,IF(TODAY()-N:N<30,0,IF(ISBLANK(L:L),IF(ISBLANK(T:T),IF(ISBLANK(X:X),1,0),0),0))))

I suggest changing 30 to a cell reference so that if you ever change your mind on the timeframe, it's a easier.

Remember, this is an arrary, so ctrl+shift+enter.

I'm sure there are better way to write this, but this was that first that came to mind.

3. ## Re: Count formula for dates greater than 30 days old with exclusions and not count blanks

This is working

This is what i am using for anything that is older than twenty eight days(changed it)

=SUM(IF(ISBLANK(N2:N999),0,IF(TODAY()-N2:N999<28,0,IF(ISBLANK(L2:L999),IF(ISBLANK(T2:T999),IF(ISBLANK(X2:X999),1,0),0),0))))

Now what do i change if i need to calculate only what would be 28 days old Tommorrow?

This i am still having trouble with, and i changed the < to a = and it did not work....

=SUM(IF(ISBLANK(N2:N999),0,IF(TODAY()-N2:N999=29,0,IF(ISBLANK(L2:L999),IF(ISBLANK(T2:T999),IF(ISBLANK(X2:X999),1,0),0),0))))

5. ## Re: Count formula for dates greater than 30 days old with exclusions and not count blanks

That did not work for closed tomorrow it gave me a value of 6, and there should only be 1 .

i really appreciate the help

6. ## Re: Count formula for dates greater than 30 days old with exclusions and not count blanks

Keeping track of the inbedded if statements was difficult, so I cleaned up the formula to look like this (Y3 = close period of 28):

=SUM(IF(TODAY()-N2:N9999=Y3-1,1,0)*IF(ISBLANK(N2:N9999),0,1)*ISBLANK(L2:L9999)*ISBLANK(T2:T9999)*ISBLANK(X2:X9999))

This works. I think when you switched to =, one of the if needed to switch from true to false. So this formula is easier to keep track of.

I've updated both formulas to be correct in the attached spreadsheet.

There are currently 1 users browsing this thread. (0 members and 1 guests)