Countif Query

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?

Find more posts tagged with

Sales

Human Resources

Finance

Formulas

Marketing

Support

IT & Operations

Product Development

Project Management

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.