Sum Row with Current Month for Metric Widget

John Walden

I've search and tried this so many different ways but cannot figure this out.

Basically, I want to create a Sheet Summary Field that totals the row for the current month. I thought creating a helper field and index formula would do it - but can't get it to work for some reason. I think I've over complicated it, honestly.

In the screenshot - I simply want to SUM the Eligible and Recommend columns for the "current month". i.e. =MONTH(TODAY()).

I don't want to manually specify the month, i.e. "April" - because this Summary Field will be a Metric Widget on a Dashboard and I want it to update automatically each month.

Primary & Helper Columns are text fields, the Date column is date field. Any help is appreciated - thanks!

Heather Duff

Hi John,

Try this:

=sumif(helper:helper,@cell=month(today()),eligible:eligible)

You would replace the last portion with recommended:recommended for the second field.

If your sheet spans more than just this year, you'll want to add a Year helper column with the formula =YEAR(date@row). Then your SUMIF formula would change to a SUMIFS formula like this:

=sumifs(eligible:eligible,helper:helper,@cell=month(today()),year:year,@cell=year(today()))

Note that, with SUMIFS, the column you want to sum is actually at the beginning rather than the end, as in SUMIF.

Hope this helps!

Best,

Heather

Heather Duff

@John Walden, what about this:

=sumif(helper:helper,@cell=month(today()),eligible:eligible)+sumif(helper:helper,@cell=month(today()),rrecommended:recommended)

I'm sure there's a shorter way of doing it, but that's what my first-thing-in-the-morning brain came up with. :)

Heather Duff

Hi John,

Try this:

=sumif(helper:helper,@cell=month(today()),eligible:eligible)

You would replace the last portion with recommended:recommended for the second field.

If your sheet spans more than just this year, you'll want to add a Year helper column with the formula =YEAR(date@row). Then your SUMIF formula would change to a SUMIFS formula like this:

=sumifs(eligible:eligible,helper:helper,@cell=month(today()),year:year,@cell=year(today()))

Note that, with SUMIFS, the column you want to sum is actually at the beginning rather than the end, as in SUMIF.

Hope this helps!

Best,

Heather

John Walden

Hi Heather,

Thank you - this certainly got closer by returning the value of one column and I can work with this as I can create another SUM Column; however, I wanted to sum Eligible and Recommended together in this formula - perhaps it isn't possible?

=SUMIF(Helper:Helper, @cell = MONTH(TODAY()), Eligible:Recommended) - this or any form of this doesn't seem to work.

Thanks again - this is helpful!

Heather Duff

@John Walden, what about this:

=sumif(helper:helper,@cell=month(today()),eligible:eligible)+sumif(helper:helper,@cell=month(today()),rrecommended:recommended)

I'm sure there's a shorter way of doing it, but that's what my first-thing-in-the-morning brain came up with. :)