# Sum Row with Current Month for Metric Widget

Options
✭✭✭

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!

Tags:

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

=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. :)

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

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!

• ✭✭✭✭✭✭
Options