Counting number of items from a specific month with multiple criteria

I'm trying to count the number of items that were published for each LOB in our operation during each month. I've tried a dozen different formulas, but I can't seem to get any to work. I currently have a sheet summary with the following:

=COUNTIFS([Received Month]:[Received Month], IFERROR(MONTH(@cell ), 0) = 1, (LOB, "Guest Relations"))

I have this as the column formula for the Received Month column:

=MONTH([Edits Received]@row)

What am I missing? Should I be using IFERROR for the months, since some of the cells do have #INVALID DATA TYPE?

Any help would be appreciated!

Find more posts tagged with

Sheets

Formulas

Project Management

Accepted answers

Paul Newcome

There are a couple of things to look at here.

First… The MONTH function is designed to look at dates. You can either evaluate the date column directly using the MONTH function or evaluate the helper column and remove the MONTH piece. I suggest the first so that you can get rid of the helper column.

We also need to get rid of the parenthesis around the second range/criteria set, and then adjust the second range so that it is actually a range.

=COUNTIFS([Edits Received]:[Edits Received], IFERROR(MONTH(@cell), 0) = 1, LOB:LOB, @cell = "Guest Relations")

If you can have multiple selections in a single cell in the LOB column:

=COUNTIFS([Edits Received]:[Edits Received], IFERROR(MONTH(@cell), 0) = 1, LOB:LOB, HAS(@cell, "Guest Relations"))

All comments

AngB789

Not sure if this is what you are looking for but I added a couple helper columns.

1st: I started with adding a LOB Count column and formula to pull the total numerical value of LOB's selected. (I wasn't sure if you could have multiple for 1 date as it looks like you have that column set to select multiple options)
Formula: =CountM(LOB@row)

2nd: I added a helper column to pull the month name from the numerical value (I have had the best luck personally with month names rather than month number)
Formula: =IF([Received Month]@row = 1, "January", IF([Received Month]@row = 2, "February", IF([Received Month]@row = 3, "March", IF([Received Month]@row = 4, "April", IF([Received Month]@row = 5, "May", IF([Received Month]@row = 6, "June", IF([Received Month]@row = 7, "July", IF([Received Month]@row = 8, "August", IF([Received Month]@row = 9, "September", IF([Received Month]@row = 10, "October", IF([Received Month]@row = 11, "November", IF([Received Month]@row = 12, "December", ""))))))))))))

3rd: I created a Month column that would be used as the metrics labeling out all the months of the year January-December:

4th: Count column

Formula: =SUMIF([Month Name Helper]:[Month Name Helper], Month@row, [LOB Count]:[LOB Count])

You can hide those helper columns on the sheet as I do that on my sheets where I pull the month name as well as the month number. Let me know if that helps! ☺️

Paul Newcome

There are a couple of things to look at here.

We also need to get rid of the parenthesis around the second range/criteria set, and then adjust the second range so that it is actually a range.

=COUNTIFS([Edits Received]:[Edits Received], IFERROR(MONTH(@cell), 0) = 1, LOB:LOB, @cell = "Guest Relations")

If you can have multiple selections in a single cell in the LOB column:

=COUNTIFS([Edits Received]:[Edits Received], IFERROR(MONTH(@cell), 0) = 1, LOB:LOB, HAS(@cell, "Guest Relations"))

MasterFred

This worked perfectly. Thank you!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.