countifs help

**shiro93** · 05-26-2015, 07:47 AM

Hello,

I would like to ask for an assistance with following formula, using countifs in excel 2013

This is so far what I've got
=COUNTIFS(F3:F12, "XY", J3:J12, "<="&K3:K12)

the first condition is working, it is simply comparing text in column F, from rows 3 - 12. The other one not so much. Once the first condition is fulfilled, it should compare dates in column J (rows 3 - 12), with dates in column K (rows 3 - 12), and once the date in J column is lesser or equal to the one in K column (comparing the same rows for both columns of course), it should higher the number by 1 (starting from 0).

I have tried it like this, also: =COUNTIFS(F3:F12, "XY", J3:J12, "<="&K8) - to compare one single cell, and it worked like a charm. Both conditions were fulfilled, and succeeded, and the number got higher by 1 (so was actually 1).

Could you please advice, how to fix the first equation, since I need to compare date from each row in column J, with each date in column K?

Thank you in advance all :-)

**Tony Valko** · 05-26-2015, 07:57 AM

Maybe this...

=SUMPRODUCT(COUNTIFS(F3:F12,"XY",J3:J12,"<="&K3:K12))

**Special-K** · 05-26-2015, 07:58 AM

Maybe the dates in column K are not dates but text.
Use ISTEXT() to test different values in column K.

**shiro93** · 05-26-2015, 08:02 AM

thanks for a quick reply, however does not work properly. From my table - The first condition is fulfilled 3 times, and the other one only once, so the actual result should be only 1. However with your equation it's throwing me 7, and once I change some date, the result is moving up/down by 1 or even 2. Any advice?