COUNTIFS Trouble / Another Function?

[Justinian]

I am trying to track when an outside contractor performs work on my job site using two metrics: date assigned and date completed. What I want is to find all occurrences of "OCD" in a worksheet, then have Excel look to the left and see if there is a date in the "Date Assigned" column and if there is a date, I want Excel to count that occurrence. In another cell, I want Excel to look in that same row and see if there is a date in the "Date Completed" column and if there is, I want Excel to count that occurrence. If you look at my workbook through the link below, in that example I want Excel to return a 2 for assigned and a 1 for completed.

I tried using COUNTIFS but it will not count cells with dates in it. Anyone know how to resolve this?

https://app.box.com/s/kkf0ye0qu9l7nsxjwknhmjje5u2dd0x7

 

[Oscar Cronquist]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Room code[/td][td]# of beds[/td][td]Date assigned[/td][td]Date completed[/td][td]Reason delayed[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

1401​

[/td][td]

2​

[/td][td]

5/10/2018​

[/td][td]

5/11/2018​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

1845​

[/td][td]

2​

[/td][td]

5/10/2018​

[/td][td]

5/11/2018​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

1059​

[/td][td]

1​

[/td][td]

5/10/2018​

[/td][td]

5/11/2018​

[/td][td]OCD[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

2231​

[/td][td]

1​

[/td][td]

5/10/2018​

[/td][td][/td][td]OCD[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]Date[/td][td]Assigned [/td][td]Completed[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

5/10/2018​

[/td][td]

2​

[/td][td]

1​

[/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet48[/td][/tr][/table]

Formula in cell B9:
=COUNTIFS(C2:C5,A9,E2:E5,"OCD")

Formula in cell C9:
=COUNTIFS(C2:C5,A9,D2:D5,"<>",E2:E5,"OCD")

 

[Justinian]

My apologies, I should have been more specific.

I do this report at the end of each week so when I want to count OCD dates assigned and completed, I am looking through the entire week (all five days of data), not just one specific date such as 5/10/18.

 

[Oscar Cronquist]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Room code[/td][td]# of beds[/td][td]Date assigned[/td][td]Date completed[/td][td]Reason delayed[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

1401​

[/td][td]

2​

[/td][td]

5/10/2018​

[/td][td]

5/11/2018​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

1845​

[/td][td]

2​

[/td][td]

5/10/2018​

[/td][td]

5/11/2018​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

1059​

[/td][td]

1​

[/td][td]

5/10/2018​

[/td][td]

5/11/2018​

[/td][td]OCD[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

2231​

[/td][td]

1​

[/td][td]

5/10/2018​

[/td][td][/td][td]OCD[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]Date[/td][td]Assigned [/td][td]Completed[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

5/7/2018​

[/td][td]

2​

[/td][td]

1​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

5/11/2018​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet48[/td][/tr][/table]

Formula in cell B9:
=COUNTIFS(C2:C5,"<="&A10,C2:C5,">="&A9,E2:E5,"OCD")


Formula in cell C9:
=COUNTIFS(C2:C5,"<="&A10,C2:C5,">="&A9,D2:D5,"<>",E2:E5,"OCD")

 

[Justinian]

Ok, I think I am still not being clear enough. There will be data, for example, for 5/7-5/12/18 and each date is in its own box as seen above. I want Excel to look through all data on the entire sheet for the words OCD, then look and count how many dates have been assigned and how many have been completed.

 

[Oscar Cronquist]

Ok, I think I am still not being clear enough. There will be data, for example, for 5/7-5/12/18 and each date is in its own box as seen above. I want Excel to look through all data on the entire sheet for the words OCD, then look and count how many dates have been assigned and how many have been completed.

I am not sure I understand, the formulas in my first post do the things you are asking for.

Another example with dates 5/7-5/12/18:

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Room code[/td][td]# of beds[/td][td]Date assigned[/td][td]Date completed[/td][td]Reason delayed[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

1401​

[/td][td]

2​

[/td][td]

5/10/2018​

[/td][td]

5/11/2018​

[/td][td]OCD[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

1845​

[/td][td]

2​

[/td][td]

5/10/2018​

[/td][td]

5/11/2018​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

1059​

[/td][td]

1​

[/td][td]

5/11/2018​

[/td][td]

5/11/2018​

[/td][td]OCD[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

2231​

[/td][td]

1​

[/td][td]

5/10/2018​

[/td][td][/td][td]OCD[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

1816​

[/td][td]

2​

[/td][td]

5/6/2018​

[/td][td][/td][td]OCD[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

1335​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

2391​

[/td][td]

1​

[/td][td]

5/9/2018​

[/td][td][/td][td]OCD[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

1878​

[/td][td]

1​

[/td][td]

5/9/2018​

[/td][td]

5/9/2018​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

1965​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]Date[/td][td]Assigned [/td][td]Completed[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]

5/7/2018​

[/td][td]

0​

[/td][td]

0​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]

5/8/2018​

[/td][td]

0​

[/td][td]

0​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]

5/9/2018​

[/td][td]

1​

[/td][td]

0​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td]

5/10/2018​

[/td][td]

2​

[/td][td]

1​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td]

5/11/2018​

[/td][td]

1​

[/td][td]

1​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td]

5/12/2018​

[/td][td]

0​

[/td][td]

0​

[/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet48[/td][/tr][/table]

Formula in B13:
=COUNTIFS($C$2:$C$10,A13,$E$2:$E$10,"OCD")

Formula in C13:
=COUNTIFS($C$2:$C$10,A13,$D$2:$D$10,"<>",$E$2:$E$10,"OCD")

 

[RoryA]

Do you perhaps mean:

Assigned: =COUNTIFS(C3:C8,"<>",$E$3:$E$8,"OCD")
Completed: =COUNTIFS(D3:D8,"<>",$E$3:$E$8,"OCD")

 

[Justinian]

Yes, both of those are now working for me.

Thank you both!