Count how many jobs start in a month

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.

Find more posts tagged with

Workspaces

Templates

Sheets

Formulas

Attachments

Integrations

Conditional Formatting

Comments and Conversations

Card View

Cell linking

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Break Up Multi-Entry Row
I'm using a form entry (into an Intake Sheet) in which someone can select multiple locations for the same Initiative. I'm looking to copy the row to a different Master sheet, but would like to have the same row copied multiple times, once per location selected. Intake Sheet: Desired Result in Master Sheet: Basically, I…
Set contents of two columns into 1
Hi - trying to figure out a formula that combines two columns into one long column, maintaining contents in multiple rows. Noting that the number of items in each column will change as these are each using the index/distinct to summarize a separate sheet. For example: Thanks for any help!
Unparsable formula; Sheet summary SUMIF formula
Can anyone help why this formula is coming up as unparseable? I'm creating a sheet summary, where it totals the Estimated GP (Est. GP) column if the Status column has the "Quoted" drop-down option is selected. Formula i've used: =SUMIF(Status:Status,"Quoted",Est. GP:Est. GP) Thanks a lot!