SUMIF within the last 30 days?

BMiller828

I am trying to sum the totals from another sheet that fall within the last 30 days. I've tried the "TODAY(-30) function but can't get it to work correctly. I'm assuming I need to add something to this formula that is currently giving me an all time total:

=SUMIF({Flight Activity Report Range 1}, MATCH(PILOT@row, {Flight Activity Report Range 2}, 0))

Danielle Arteaga

Hi. Do you have a date field in one of your sheets? You'll need to convert this to a SUMIFS, and include the date field as part of your calculation.

Let's say your Flight Activity Report looks like this:

On your summary sheet, where you are calculating flight time in last 30 days, you can use this formula:

=SUMIFS({Flight Activity Report Flight Minutes}, {Flight Activity Report Pilot}, Pilot@row, {Flight Activity Report Flight Date}, >=TODAY(-30))

Placing this formula in the "Minutes Flown in Last 30 Days" column will calculate the total flight time for each pilot in the Pilot column for the past 30 days.

You can alter this to suit whatever columns you're working, but I encourage your to name your cross-reference ranges instead of using Smartsheet's default values of "Range 1", "Range 2", etc. That way, you can more easily find errors in your formulas and you can visually track the logic of them later.

BMiller828

That appears to have worked. Thx!

BMiller828

Would you mind explaining how to rename the cross sheet references? Everytime I try and change from the generic "Range 1, Range 2" etc., The formula doesn't work anymore.

Thx!