We help IT Professionals succeed at work.

COUNTIFs from two columns

AFGPHXExcel
AFGPHXExcel used Ask the Experts™
on
I am trying to count the number of dates that are greater than the corresponding date in the same row. I tried a COUNTIF but it is just looking if the date is great than any of the dates, so its not working. Here is the formula: =COUNTIFS( Date_Due_To_HQ,">"& Sent_By_Tertiary)

So if the date in Date_Due_To_HQ is greater than the corresponding date in Sent_By-Tertiary then I want it counted. Any help would be much appreciated!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Neil RussellTechnical Development Lead

Commented:
You need something like.....

=countif(A1:A20,">"&B1)

That assumes your column A has date values in cells A1 through A20 that you want to check against and that your cell B1 has the date you want to compare with
Most Valuable Expert 2013

Commented:
You can't do that very easily with COUNTIFS - try SUMPRODUCT, i.e.

=SUMPRODUCT((Date_Due_To_HQ>Sent_By_Tertiary)+0)

regards, barry

Author

Commented:
Neither gave me the solution... What I am trying to do is compare the sent date to the date due to determine if they are late, and if its late I want to count them.  The due date is variable in each row.
Neil RussellTechnical Development Lead

Commented:
Then add a column? =if(Date_Due_To_HQ>Sent_By_Tertiary,1,0)
And at the bottom of it do a sum!
Most Valuable Expert 2013
Commented:
The formula I suggested should work if your ranges are the same size. I names A2:A10 as Date_Due_To_HQ and B2:B10 as Sent_By_Tertiary
 and then used the formula I suggested above, i.e.

=SUMPRODUCT((Date_Due_To_HQ>Sent_By_Tertiary)+0)

See E2 on the attached

Note that that counts based on what you suggested in your question, i.e. "So if the date in Date_Due_To_HQ is greater than the corresponding date in Sent_By-Tertiary then I want it counted". Your subsequent reply seems to suggest that might need to be the other way round....if so then swap the > for <

regards, barry
27242060.xlsx