Sum Row with Current Month for Metric Widget

04/07/21
Accepted

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!

Popular Tags:

Best Answers

  • Heather DHeather D ✭✭✭✭✭
    Accepted Answer

    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([email protected]). 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 DHeather D ✭✭✭✭✭
    Accepted Answer

    @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

  • Heather DHeather D ✭✭✭✭✭
    Accepted Answer

    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([email protected]). 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 WaldenJohn 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 DHeather D ✭✭✭✭✭
    Accepted Answer

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

Sign In or Register to comment.