Count cells containing specific text when adjacent cell contains datewithin a range...

[kiel wymer]

Hey,

Got a manning sheet, need to be able to show number of cells that contain 'RTU' when the next cell contains a date within a month (oct, nov, dec etc).

K L M
5
6 PLACE 1 RTU 13/11/2013
7 LTA 12/06/2014
8 RTU 31/10/2013
9 PLACE 2 RTU 14/11/2013

so for example in this case I would need to output "2" if the criteria was L5:L9=RTU and M5:M9=*/11/2013

Hopefully i've explained this in a somewhat useful way...

Thanks!

 

[kiel wymer]

Sorry, layout got a bit junky.

Column K is not included in the sum
Column L will always contain LTA/RTU or be blank.
Column M will always be a date if column L contains LTA/RTU, otherwise it will always be blank.

 

[Claus Busch]

Hi,

Am Thu, 12 Sep 2013 03:08:11 -0700 (PDT) schrieb kiel wymer:

6 PLACE 1 RTU 13/11/201
7 LTA 12/06/2014
8 RTU 31/10/2013
9 PLACE 2 RTU 14/11/2013

so for example in this case I would need to output "2" if the criteria was L5:L9=RTU and M5:M9=*/11/2013

try:
=SUMPRODUCT(--(L5:L9="RTU"),--(MONTH(M5:M9)=11),--(YEAR(M5:M9)=2013))


Regards
Claus B.

 

[kiel wymer]

Hey, Got a manning sheet, need to be able to show number of cells that contain 'RTU' when the next cell contains a date within a month (oct, nov, dec etc). K L M 5 6 PLACE 1 RTU 13/11/2013 7 LTA 12/06/2014 8 RTU 31/10/2013 9 PLACE 2 RTU 14/11/2013 so for example in this case I would need to output"2" if the criteria was L5:L9=RTU and M5:M9=*/11/2013 Hopefully i've explained this in a somewhat useful way... Thanks!

This worked brilliantly. Thank you very, very much. Saved me hours trying to work it out

 

[kiel wymer]

Hi, Am Thu, 12 Sep 2013 03:08:11 -0700 (PDT) schrieb kiel wymer: > 6 PLACE 1 RTU 13/11/201 > 7 LTA 12/06/2014 > 8 RTU 31/10/2013 > 9 PLACE 2 RTU 14/11/2013 > > so for example in this case I would need to output "2" if the criteria was L5:L9=RTU and M5:M9=*/11/2013 try: =SUMPRODUCT(--(L5:L9="RTU"),--(MONTH(M5:M9)=11),--(YEAR(M5:M9)=2013)) Regards Claus B. --Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2

This worked brilliantly. Thank you very very much, saved me hours!