Countif Query

grant53396

Hi,

I currently have the following formula which is used for counting how many jobs we intend to complete on a day:

=COUNTIF({Realistic Completion Date 4}, {Realistic Completion Date 4} = TODAY())

This works fine, although there is a scenario where we often add things to the job, which is identified by a letter A in the job number. I need a formula that counts how many will complete except the ones that have an A in the job number...

I was thinking of something like =IF("job number contains "A"", "",COUNTIF({Realistic Completion Date 4}, {Realistic Completion Date 4} = TODAY())) But i am not sure how to do the first bit of the formula... can anyone help?

 

Paul Newcome

=IF(FIND("A", [Job Number]@row) > 0, "", COUNTIF({Realistic Completion Date 4}, {Realistic Completion Date 4} = TODAY()))

 

Give this a try.

grant53396

thanks Paul.

I have tried that but it comes up saying #UNPARSEABLE. Is this because I am trying to reference a whole column, or should it work anyway? See attached image.

 

Paul Newcome

Where is the Job Number column? On the sheet with the formula or a different sheet?

grant53396

This is on a seperate sheet as a reference. does this present an issue?

Paul Newcome

Try something like this...

 

=COUNTIFS({Realistic Completion Date 4}, TODAY(), {Job Number 4}, FIND("A"< @cell) > 0))

grant53396

Thanks Paul; really appreciate your time, but it is still coming up saying unparseable? Can i maybe share the sheet so you can see if there is something i am doing wrong? 

The calculation sheet is: https://app.smartsheet.com/sheets/JXHGqGVF7Cj7q8w3MmrWhmrq6JvrcmVXx6fFX3C1

The data source sheet is: https://app.smartsheet.com/sheets/g3fqjXWfCGJ8WRX4Jhp8pp6Vg2CvMVxp8xg5rrw1