Current Month Data Pull

Kostya V

Hello,

I created a scorecard system to manage performance for our vendor base. I am attempting to pull a report of average rating within the current month. I was able to put a formula together, which is honestly intense, and it worked for the month of November, but as soon as December 1st hit, it wouldn't work. I believe my formula is a bit over the top and can be simplified and made operational.

Current formula is as follows:

=IF(COUNTIFS({Installer}, Installer@row, {EntryDate}, >=DATE(YEAR(TODAY()), MONTH(TODAY()), 1), {EntryDate}, <DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1)) > 0, SUMIFS({Rating}, {Installer}, Installer@row, {EntryDate}, >=DATE(YEAR(TODAY()), MONTH(TODAY()), 1), {EntryDate}, <DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1)) / COUNTIFS({Installer}, Installer@row, {EntryDate}, >=DATE(YEAR(TODAY()), MONTH(TODAY()), 1), {EntryDate}, <DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1)), "N/A")

However, it's way too much and doesn't work as it should.

I was thinking of starting a new formula with the following:

=AVERAGEIF({Installer}, Installer@row, {Rating})

But need to add the "in the month" option that pulls data for only current month, and keeps updating as the new months come along.

{Installer} - Name of installer (multiple line items that need to be averaged within the month)

{Entrydate} - Date of when the score card was entered. This is where we will pull the current month from.

{Rating} - The rating per line item that needs to be averaged within the current month.

Paul Newcome

Are you able to provide some screenshots for context?

Kostya V

https://community.smartsheet.com/discussion/comment/407982#Comment_407982

Hi Paul,

Sure thing, but I also believe I figure out a simpler formula which works now, but I'm hesitant to say for sure if it will work for next month.

Here's the new Formula on a new sheet where I'm consolidating specific data:

Here's where the raw data drops in via a form that our PMs fill out:

For reference, I am only using the Entry Date data set, not job start or job end dates.

Paul Newcome

That should work assuming you will not have an entry date in the next month.

Kostya V

https://community.smartsheet.com/discussion/comment/407994#Comment_407994

I ran a test, and put a test line for next month, formula did not capture it, most likely because it's pulling only the current month data, which makes me believe the formula is working correctly. So I would not know if it will work next month until January 1st comes.

Now i'm working to resolve two issues:

1. Anything that has no data or is at "0" should return N/A. I believe I can fix that fairly easy.
2. My other issue now is that for some reason under the "training" column, it's not giving me the right info, and I assume it's because there is "N/A". I'm now trying to figure out how to average only the numbers points and exclude the N/A. (this can be seen in screenshots provided above).

Lauren Stedman

Hi!

Similarly, I am trying to capture data in a page summary field using an IF format. My sheet has two columns: Month, formatted as date, and Investment Forecast. I want the summary field to look at the Month column and determine if the date in that month column is the current month, to bring over the Investment Forecast $ amount into the summary field.

Using the AI feature to write a prompt, I received this formula. However, it comes back as 'unparsable' every time.

=IF(MONTH([Month]@row) = MONTH(TODAY()), [DMS Forecast]@row, "")

I tried to use another method. First, made a summary field that calculated the current month's associated number using the 'Month' function" =MONTH(TODAY())

Then set up a summary field with an IF statement: =IF((MONTH(Month1)) = [Today's Month]#, [DMS Forecast]1) - this worked but when I tried to make it an IFs statement and build in the logic for each row, I received another error.

Genevieve P.

Hi @Lauren Stedman

=IF(MONTH(Month@row) = MONTH(TODAY()), [DMS Forecast]@row, "")

Should work! As long as your column names are Month and DMS Forecast

If it still gives you unparseable, can you show a screen capture of your sheet with the column names showing? But block out sensitive data.

Thanks!

Genevieve

Lauren Stedman

Hi @Genevieve P.

Thanks for the quicky reply. I didn't articulate my issue well. I can get this to work for one IF statement but not for an IFs statement:

I need the summary field to look at all the months and if it's the current month, bring me the DMS forecast associated with that row. When I make it an IFs statement, it fails.

Paul Newcome

@Lauren Stedman The screenshot helps with the context. Try this:

=INDEX([DMS Forecast]:[DMS Forecast], MATCH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), Month:Month, 0))

Genevieve P.

Hi @Lauren Stedman

Smartsheet does not have an IFS function with an S - you'll want to use what's called a "nested IF" statement instead, where you list multiple possibilities as individual IF statements in the order you want it to check.

For example:

=IF(MONTH(Month1) = MONTH(TODAY()), [DMS Forecast]1, IF(MONTH(Month2) = MONTH(TODAY()), [DMS Forecast]2, IF(MONTH(Month3) = MONTH(TODAY()), [DMS Forecast]3.... and so on.

However based on how your sheet is set up, I'd actually recommend using an INDEX function instead. You can index the DMS forecast column and return a value from that column based on the row with the current month.

Try:

=INDEX(COLLECT([DMS Forecast]:[DMS Forecast], Month:Month, MONTH(@cell) = MONTH(TODAY())), 1)

If you get an error, this may be due to blank cells in your Month column or cells with text. Try wrapping the MONTH(@cell) portion in an IFERROR:

=INDEX(COLLECT([DMS Forecast]:[DMS Forecast], Month:Month, IFERROR(MONTH(@cell), 0) = MONTH(TODAY())), 1)

See: Lookup one cell using multiple criteria

Cheers,

Genevieve

Genevieve P.

https://community.smartsheet.com/discussion/comment/422218#Comment_422218

Haha or Paul's suggestion! 🙂

Paul Newcome

@Genevieve P. There's that COLLECT function again. 🤣

Lauren Stedman

Thanks @Genevieve P. & @Paul Newcome

The Index with no collect reads as #NO MATCH

=INDEX([DMS Forecast]:[DMS Forecast], MATCH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), Month:Month, 0))

The Index WITH Collect returns #Incorrect Argument Set, even with the error wrapper

=INDEX(COLLECT([DMS Forecast]:[DMS Forecast], Month:Month, MONTH(@cell) = MONTH(TODAY()), 1)

=INDEX(COLLECT([DMS Forecast]:[DMS Forecast], Month:Month, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), 1)

However, the stacked IFs seem to do the trick so I'll use that for my immediate need and keep working on the others for a cleaner formula. Thank you!!

Genevieve P.

Hey @Lauren Stedman

The cell that has today's month (February) listed has the 26th of February, versus the 1st of February. If you change the date in the cell it should find a match 🙂

Just a side note - there's no March row, either, is that intentional?

Genevieve P.

For the INDEX(COLLECT, my apologies! There's a missing closing parentheses. If you prefer that method, try it again with 3 ))) before the 1

=INDEX(COLLECT([DMS Forecast]:[DMS Forecast], Month:Month, IFERROR(MONTH(@cell), 0) = MONTH(TODAY())), 1)

) one for TODAY

) one for MONTH

) final one for COLLECT

Lauren Stedman

Hi @Genevieve P.

Re Feb 26, I picked a random date to see what it would do if there were TWO lines for February and threw that date over March. Why does the day of the month impact the formula? Mostly just curious.

The new parentheses worked! Thank you, that's much cleaner to look at and copy into other summaries to collect other fields.

I appreciate your help!