Forumla that counts dates withing a specific month only

Platform discussions Special workflows & use cases
, ,
1

Have a formula question. What formula could I utilize to count only dates within the current month and year or that reference another cell that could create a board that looks similar to this (KAE would be item) without having to filter down by person and timeframe one by one and where every person on the referenced board is listed as rows of items:

image
image1265Ă—183 20.6 KB

Currently users are entering dates in columns on items to indicate activity. They are automatically assigned to a People column titled {KAE} when they enter a new item. Looking for a way to quickly summarize by person individual column totals for each month, current and archive view. So the person located in the {KAE} column would be an item, then columns {KM360}, {Survey}, {Final MMS} below would be columns that are summing the number of entries made in the designated period. I understand I can use filters, however my staff needs to be able to see everything at a glance versus filtering down to one person at a time.

image
image497Ă—574 24.4 KB

So from the above image the formula would recognize by person, the number of dates in each column. KM360 = 16, Survey = 12, Final MMS = 9 and the person in the {KAE} column would be an item. Making sense?

In Smartsheet I did it this way:

=COUNTIFS({KAE Call Report - Brandon Swaner (9GG) Range 1}, IFERROR(MONTH(@cell), 0) = Survey1, {KAE Call Report - Brandon Swaner (9GG) Range 1}, IFERROR(YEAR(@cell), 0) = Survey2)

“KAE Call Report - Brandon Swaner (9GG)” = Referenced sheet
“Range 1” = Column on referenced sheet I wanted to “count”
“Survey 1” - Cell with current month numeral
“Survey 2” - Cell with current year

2

Have you tried using a dashboard to accomplish this?
With a chart, you can set a Date column as the X axis, then group the dates by Month.
With a stacked bar chart, you can stack by Person, then you’ll see the totals of each person month-by-month. You can set the bars to stack on top of each other or show side by side.

In monday there is a lot you can do in dashboards, you just need to think of it differently than Excel or Smartsheets. In spreadsheets you calculate / display everything on the sheet, but in monday not everything is done in the main table view.

1 Like
3

Considered it but not ideal for staff. Agree there are many ways to approach it! I am confident a formula can be figured out - with a lot of help :slight_smile:

4

Anyone have a solution? Use General Caster for this perhaps?

5

Hi @AK43040 ,

You may try our Smart Spreadsheet for monday app.

You’ll be able to import an existing board to the spreadsheet, then work with the data using all the Excel-like features (cell formulas, pivot tables, filtration, conditional formatting, etc.).

Hope it may help your case.

Best regards,
Katerina Kovriga, Stiltsoft

3 Likes
6

@askstratman sharing for historical context and per conversation on CC tracking formula automation