Help with Countif formula

[willow1985]

I have the below formula, I must be having an off day as I cannot see what I entered incorrectly.

I want the formula to count all the cells with values in column A (all of which are numbers) only if the corresponding column D does not have the text "Completed"

My formula is still is counting all values in column A whether the cell beside in Column D has the word "completed" or not.

Thank you to anyone who can help a tired girl out

=COUNTIFS('Manual Log'!$A3:$A1000000,"<>",'Manual Log'!$D3:$D1000000,"<>""Completed")

 

[willow1985]

Nevermind. Brain just kicked into gear:

=COUNTIFS('Manual Log'!$A3:$A1000000,"<>",'Manual Log'!$D3:$D1000000,"<>*Completed*")

Thank you to anyone who wasted their time on this post ?

 

[willow1985]

Actually..... if the word "Completed" was a cell reference like E1, how would that be written?

=COUNTIFS('Manual Log'!$A3:$A1000000,"<>",'Manual Log'!$D3:$D1000000,"<>*E1*")
=COUNTIFS('Manual Log'!$A3:$A1000000,"<>",'Manual Log'!$D3:$D1000000,"<>E1")

Both do not work

 

[Eric W]

You can write it as:

=COUNTIFS('Manual Log'!$A3:$A1000000,"<>",'Manual Log'!$D3:$D1000000,"<>*"&E1&"*")

or even

=COUNTA('Manual Log'!$A3:$A1000000)-COUNTIF('Manual Log'!$D3:$D1000000,"*"&E1&"*")

 

[willow1985]

Brilliant! Thank you! ?