Sum Row with Current Month for Metric Widget
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!
Best Answers
-
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, 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. :)
Answers
-
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
-
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!
-
@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. :)
Help Article Resources
Categories
Check out the Formula Handbook template!
