SUMIF with percentages for previous and current months

David Acord

Hi all,

I'm trying to work out the formula for what (in my mind) seems straightforward, but I'm having some issues achieving my goal. Can anyone help?

Here's what I'm trying to achieve:

1. If the First day of month column has a date that is the current month , then look at the primary content developer name, and display the actual utilization for that person for the current month.
2. In the screenshot below, since the current month is March, I would want the summary to display "8%"

I've highlighted these as PURPLE.

My second formula would be similar:

1. If the First day of month column has a date that is the previous month , then look at the primary content developer name, and display the actual utilization for that person for the previous month.
2. In the screenshot below, since the current month is March, I would want the summary to display "2%"

I've highlighted these as RED.

The idea of using formulae like this is that I could display them in a report and make a graph, and it would always pull the current and previous month, as opposed to me having to manually update the data each month.

I've tried cobbling some formulae from the community together, but they just display "0" so I don't know what I'm doing wrong - I'm not formula-savvy like so many folks here.

Help me please?

Formula for current month that shows "0" no matter what:

=SUMIFS([Actual Utilization]:[Actual Utilization], [First day of month]:[First day of month], (AND(IFERROR(MONTH(@cell), 1) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)), IFERROR(YEAR(@cell), 1) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)))), [Primary Content Developer]:[Primary Content Developer], CONTAINS("David Acord", @cell))

Jeff Reisman

@David Acord

Have you though about creating a metrics sheet to use as the basis of your report? That could be a lot easier.

Create a sheet with a "Developer" contact list column just like your Primary Content Developer column. Add a "Date" date column, and an "Actual Utilization" column.

Make your list of developers in the developer column, twice (one set for previous month, one set for the current month.

In the Date column for the top set, you want to build the date value for the first day of the previous month:

=IF(MONTH(TODAY()) = 1, DATE(YEAR(TODAY()) - 1, 12, 1), DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1))

English: If it's currently January, make this date value December 1st of last year, otherwise, make it the first day of the month before this one.

In the Date column for the bottom set, you want to build the date for the first day of the current month:

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

In the Actual Utilization column, we're going to create cross-sheet references to your data sheet and get each developer's utilization %. Start by typing =INDEX(COLLECT( and when you get to here, Smartsheet will pop up a helper box. Click on "Reference Another Sheet" and select your data sheet, and then select the Actual Utilization column by clicking on it's header. Give the reference a meaningful name. Smartsheet inserts the reference in curly brackets. We're going then repeat this for the criteria ranges.

The syntax is: =INDEX(COLLECT({range you want data from}, {Criteria range 1}, criteria 1, {Criteria range 2}, criteria 2), 1)

=INDEX(COLLECT({DataSheet Actual Utilization Column}, {Datasheet Primary Developer column}, Developer@row, {Datasheet First day of month column}, Month@row), 1)

In English: Collect the value from the datasheet's Actual Utilization column where the datasheet's primary developer value equals the developer on this row, and where the datasheet's first day of the month value equal the Date on this row.

Your result is a grid with two sets of date values, two sets of developers, and utilization numbers that match those from your datasheet. And these will always reflect the previous month and the current month, without any need to update formulas. Using this metrics sheet as the basis for a chart or graph means the chart or graph always stays current.

Jeff Reisman

@David Acord

Have you though about creating a metrics sheet to use as the basis of your report? That could be a lot easier.

Create a sheet with a "Developer" contact list column just like your Primary Content Developer column. Add a "Date" date column, and an "Actual Utilization" column.

Make your list of developers in the developer column, twice (one set for previous month, one set for the current month.

In the Date column for the top set, you want to build the date value for the first day of the previous month:

=IF(MONTH(TODAY()) = 1, DATE(YEAR(TODAY()) - 1, 12, 1), DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1))

English: If it's currently January, make this date value December 1st of last year, otherwise, make it the first day of the month before this one.

In the Date column for the bottom set, you want to build the date for the first day of the current month:

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

In the Actual Utilization column, we're going to create cross-sheet references to your data sheet and get each developer's utilization %. Start by typing =INDEX(COLLECT( and when you get to here, Smartsheet will pop up a helper box. Click on "Reference Another Sheet" and select your data sheet, and then select the Actual Utilization column by clicking on it's header. Give the reference a meaningful name. Smartsheet inserts the reference in curly brackets. We're going then repeat this for the criteria ranges.

The syntax is: =INDEX(COLLECT({range you want data from}, {Criteria range 1}, criteria 1, {Criteria range 2}, criteria 2), 1)

=INDEX(COLLECT({DataSheet Actual Utilization Column}, {Datasheet Primary Developer column}, Developer@row, {Datasheet First day of month column}, Month@row), 1)

In English: Collect the value from the datasheet's Actual Utilization column where the datasheet's primary developer value equals the developer on this row, and where the datasheet's first day of the month value equal the Date on this row.

Your result is a grid with two sets of date values, two sets of developers, and utilization numbers that match those from your datasheet. And these will always reflect the previous month and the current month, without any need to update formulas. Using this metrics sheet as the basis for a chart or graph means the chart or graph always stays current.

David Acord

Jeff, this looks incredible! Thank you! As soon as I can actually get to this and implement it, I'll let you know! thanks so much!