Count how many jobs start in a month

grant53396

I am trying to create a formula that counts how many jobs are due to start in a month, that havent already been started. I have created the below formula to try and count the jobs in the current month:

=COUNTIFS({Tippa JC Status}, ="", {Tippa Start}, MONTH({Tippa Start}) = MONTH(TODAY()))

 

Then I try and modify it to the following for the following month, and then the month after that:

=COUNTIFS({Tippa JC Status}, ="", {Tippa Start}, MONTH({Tippa Start}) = MONTH(TODAY(30)))

=COUNTIFS({Tippa JC Status}, ="", {Tippa Start}, MONTH({Tippa Start}) = MONTH(TODAY(60)))

However, none of these are working currently - what am i missing out?

NB: the Tippa JC status is looking up to another sheet to check that the completion date is blank. Tippa Start references the start date on the same sheet that the Tippa JC Status pulls from.

 

 

Genevieve P.

Hi Grant,

The way you build your formula will depend on the exact criteria you are looking for, or how you define a "month" (if you mean in terms of days or in terms of month name). Based on the formulas you are writing, it seems that you are looking for jobs within the next 30 days, or then within 30 - 60 days, which could be counted by using the following formula structure:

 

=COUNTIFS({Tippa JC Status}, ="", {Tippa Start}, >=TODAY(), {Tippa Start}, <=TODAY(30))

This will count anything that has a blank status between Today and Today+30 days.

 

=COUNTIFS({Tippa JC Status}, ="", {Tippa Start}, >=TODAY(30), {Tippa Start}, <=TODAY(60))

This will count anything that has a blank status that is between 30 days from now, but less than 60 days from now.

 

Keep in mind that if you are in the middle of the month (such as now) this will be counting half of November and half of December. Let me know if that's not what you were looking to do; in this case, it may be helpful to see screen captures of your two sheets, but please hide any sensitive data.

Thanks!

Genevieve

Thusitha Pradeep

Hi Genevieve P,

I need to count jobs in various stages under monthwise. Can you help me out? I can share asheet with you.

Genevieve P.

Hi @Thusitha Pradeep

I'm happy to help! However it may be best to keep the discussion in the Community; that way other users who have similar questions might be able to figure out their own solution from what we come up with.

The COUNTIFS function is fairly straight forward:

=COUNTIFS({Column in other sheet}, "Criteria", {2nd Column in other sheet}, "2nd Criteria".... etc

What may change is how your Criteria is set up in this current sheet. For example, if you have all the jobs listed down one column, then you can use the cell in that row from that column as your criteria for the job:

=COUNTIFS({Job Column in other sheet}, [Job Column]@row, etc...

Then if you're looking for specific Months within a DATE column in the other sheet, you can use the MONTH function to look for a set month (versus using TODAY as I did in the first post, here):

{Date Column in other sheet}, MONTH(@cell) = 1

^This is looking for 1, or January. You may want to wrap an IFERROR function around the month to make sure if there are blank cells it doesn't throw an error, like so:

{Date Column in other sheet}, IFERROR(MONTH(@cell), 0) = 1

Does this make sense?

Let me know if you're able to build your formula from this, or if you need more help. Should you want more help, it would be useful to see a screen capture of your sheet in Community (but please block out any sensitive data).

Cheers!

Genevieve

Sue Hill

Was looking to accomplish the same:

=COUNTIFS({Review Status}, "Resolved", {Review Status}, "No Action Needed", {Date Created}, (IFERROR(MONTH(@cell), 0) = 4))

2 criteria, in the Review status column on another sheet - "Resolved" and "No Action needed" are the status types that need to be counted, in a given month (month and year data in separate columns each called "Month Number" and "Year").

Genevieve P.

Hi @Sue Hill

Since you're looking in the same column for two possible criteria, you'll need to add in an OR function.

Try this:

=COUNTIFS({Review Status}, OR(@cell = "Resolved", @cell = "No Action Needed"), {Date Created}, IFERROR(MONTH(@cell), 0) = 4)

Let me know if this works for you!

Cheers,

Genevieve