Excel Formula - Count Column M Date If Column E Date Has Passed

0
votes

Im trying to count the amount of dates in Col M that have passed Col E

For example 

Col E 
Due Date 19/03/17

Col M
Completed Date 23/03/17

I tried 

=COUNTIF(M:M,M:M>E:E)

but this didn't work. It doesn't seem to like adding the column values. I managed to do cell to cell but when it comes to columns it really doesn't want to work. Its probably simple but giving me serious headache ATM.

ADDITIONAL:

The formula im trying to change is:

=COUNTIFS($M:$M,"<="&G6,$M:$M,">"&F6)

G6=The date of 24/03/17

F6=The date of 17/03/17

Im trying to get it to count the days between these two ranges that are completed outside of the requested date if that makes sense.

1
excelexcel-formula
your M isn't a date. - Nathan_Sav
In case your date in column M is a Typo, then use the Formula =COUNTIF(M:M,">"&E6) (not in Column M), where E6 holds the date criteria (in your example 19/03/17) - Shai Rado
Countif criteria doesn't work like this. Add another column with =M1>E1, pull down, and countif the TRUE values. Or you can piss blood and do it with an array formula. - vacip
Sorry was a Typo :) I have tried that as well @ShaiRado but unfortunately doesnt work either. - Shawn Cartwright
E has a header Due Date then dates underneath and M Completed Date then dates underneath. So the cells below the headers are DATE ONLY. Cells are formatted as short date. The formula can be anywhere but atm is in Y1. - Shawn Cartwright

1 Answers

1
votes

Assuming your column headers are as you say, you can use the following formula:

=SUMPRODUCT(N(M:M>E:E))

For speed, you may want to decrease the number of rows counted.

If your column headers might be different, I would suggest using either a dynamic named range, or a "Table" so as to exclude the header and include only the used rows. Something like:

=SUMPRODUCT(--(CompletedDateTable>DueDateTable))

Note that in the first formula, I used the N function, and in the second, I used the double unary operator. Just two different ways of accomplishing the same thing of converting TRUE FALSE to 1 0.