Calculating Number of Items in a Month based on System Generated date field

Andrea Mayer

Hello all -

Needing help with a formula to calculate how many requests are submitted via a webform for each month.

2 questions related to this:

1. Can a formula pull just the month from a System Generated cell: Created by (Date)?

2. Where are errors in this formula:

=COUNTIF({Date Submitted}, MONTH(@cell) = 1)

'Date Submitted' is the column name and I have tried it as a regular Date column and System Generated.

Thank you!

Andrea

Find more posts tagged with

Formulas

Services

Project Management

Comments

TeeM

Watching with interest

Paul Newcome

1. Yes it can.

2. There are no issues with the formula itself, but any blank cells within a range (including blank rows at the bottom of the sheet if the entire column is selected) will through an error for the MONTH function.

To avoid this we can wrap the MONTH function in an IFERROR. This will allow us to designate a number that will essentially "replace" the missing month when a cell within the range does not have a date in it.

Give this one a whirl...

=COUNTIF({Date Submitted}, IFERROR(MONTH(@cell), 0) = 1)

This replaces the missing month with the number zero. Because there are no month numbers of zero, this will not throw any false positives. In all reality, you can use any number that is not a month number, so 0 and then 13 through infinity. Any numbers 1 through twelve would need to have a decimal. 0 is just the easiest one to use because you can also use this in a YEAR, WEEKNUMBER, DAY, etc... Any function that requires a number really.

Andrea Mayer

Thank you, Paul! This worked perfectly. I appreciate the quick and helpful response.

Paul Newcome

Happy to help!

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…