Countif formula help required

[Quinton]

Hello please help!!

I have the following data (small example with all scenarios):
14-04-14 < empty >
16-12-11 12-12-11
< empty > 12-11-10
< empty > < empty >

Basically either dates or < empty > in either 2 columns.

I would like to count "1" in Column "C" if Column "A" has a date & Column "B" has no date. Otherwise in any other event count "0".

So the results (in column "C") would be as follows for the data above:
1
0
0
0

Many thanks in advance

 

[VoG]

Try

=IF(AND(A1<>"",B1=""),1,0)

 

[Quinton]

Many thanks for your help. But this did not work. I applied your formula to my spreadsheet in the following way:

=IF(AND(C8<>"",D8=""),1,0)

C8 & D8 being the actual columns in my sheet.

Any ideas or did I do something wrong.

Angain many thanks!

PS - thought I should mention that < empty > is actually in the cell!

 

[Quinton]

Funny I just edited my previous post saying that the cells are not blank they actually show < empty >.

Hope this helps!

Thanks again

 

[VoG]

In that case

=IF(AND(C1<>"< empty >",D1="< empty >"),1,0)

 

[Quinton]

Thanks again, nearly there I think - as it still does not work. It is returning "0" in every case.

I think the problem is that it needs to count "1" if a date exists in column "C" and "< empty >" exists in column "D".

As per my original Post

Sorry hope this helps?

Again many thanks

 

[Quinton]

Whoops - Very sorry ignore last post as it works perfectly!!!!!

Many thanks for your help!!!!

Kind regards

Quinton

 

[Quinton]

Hello again!

Sorry but I am just trying to apply your last formula to another one I am trying to sort out.

I now have the following formula:

=IF(AND(E8<>"< empty ><EMPTY>",C8="< empty ><EMPTY>"),1,0)

I need to apply it to the following data:

Column C Column E
< empty > 09-09-10
< empty > < empty >
14-04-14 < empty >
16-12-11 18-12-11
12-05-11 08-09-10

With the following results being shown:

1
0
0
1

The difference is the following:

Line 4 2nd columns date happens before the 1st column date so it now counts it.

Is this one possible as it is far too complex for my small brain?

Thanks again in advance!!!!

 

[VoG]

Perhaps

=IF(AND(ISNUMBER(C1),ISNUMBER(E1),C1<E1),1,IF(AND(C1<>"< empty >",E1="< empty >"),1,0))

 

[Quinton]

Absolutely Perfect!

Many thanks!!!