# Countif Query

Options
edited 12/09/19

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?

• ✭✭✭✭✭✭
Options

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

Give this a try.

• Options

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.

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

Try something like this...

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

• Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!