Formula adjustment for a Monthly sum versus yearly?

kmorsesmartsheet

Hello,

I am trying to implement a look back to December 2024 figures - formula below totals 2024 entire year. I know I'm overlooking where to insert the month, nothing seems to be working as I play around with this. Any suggestions? I've got to be missing something very simple.

Exiting Formula for total 2024 data:

=SUMIFS({F-10.1.1-02 Quality Summary Nonconformity  Range 2}, {Quality Summary Range 1}, [Look Up Fields]@row, {Quality Summary Range 5}, ISDATE(@cell ), {Quality Summary Range 5}, YEAR(@cell ) = YEAR(TODAY() - DAY(TODAY()) - 1) - 1)@cell

Paul Newcome

You don't insert it anywhere. It is a stand-alone formula specifically for troubleshooting. It is most certainly not the final formula.

Paul Newcome

Take a look at my response on your other thread here:

https://community.smartsheet.com/discussion/comment/472389#Comment_472389

kmorsesmartsheet

Hi Paul, This comes up with #unparseable - any other suggestion?

Paul Newcome

I have a typo. The first IFERROR is misspelled. Fixing that should fix the issue.

kmorsesmartsheet

Closer - I think . . . It's throwing a '0' result now.

Paul Newcome

Try applying a filter to the source sheet that mimics the formula. If there are rows shown, a screenshot of the filter will help.

kmorsesmartsheet

Formula pulling in data from refSheet.png

Filtered refSheet Dec2024.png

UNFiltered refSheet 2024.png

Not sure if these help - I've filtered the ref. sheet so only Dec 2024 data is represented, Screen shot doesn't show entire sheet columns but hopefully enough to get the gist - in Dec.24 only 11 customers orders were listed. Of Note the top customer on the sheet QS Customer Summary Monthly is pulling the ref data into did not have sales at all in Dec. - the formula is pulling the entire year's data therefore showing this customer with $10,485.00 of sales rather than either not listing them at all since they had no sales or a $0. I'm told by the user of this sheet (who didn't build it and is unclear of what data columns are being used as references) that it generally lists by the top customers for that month and then this data is fed from QS Customer Summary to her monthly dashboard. Sorry for the lengthy reply - you are much appreciated!

VBAGuru

Below is something similar to what I have. It can be made more dynamic if needed.

=SUMIFS({F-10.1.1-02 Quality Summary Nonconformity  Range 2}, And(year{Quality Summary Range 1}=2024, Month{Quality Summary Range 1}=12)

I am presuming that Range 1 is looking at the date field and those are formatted as dates. If not, then…

=SUMIFS({F-10.1.1-02 Quality Summary Nonconformity  Range 2}, And(value(right({Quality Summary Range 1},2)=24, value(left({Quality Summary Range 1},2)=12)

I have not tried out the second one, but it would be a valid formula in Excel which seems to work most of the time here.

Paul Newcome

Did you try the SUMIFS I suggested?

kmorsesmartsheet

I did yes Paul and it throws a 0 result after fixing the typo you mentioned. Screen shots were after applying a month filter for the Dec. dates on the actual reference sheet.

Paul Newcome

What about a screenshot of the filter you are using?

kmorsesmartsheet

December Filter.png

Paul Newcome

Lets try this to narrow things down.

=COUNTIFS({Quality Summary Range 1}, [Look Up Fields]@row)

What do you get from that?

kmorsesmartsheet

Sorry Paul for my delayed response - been in and out of meetings this morning.

I'm not quite certain where I insert this into the existing formula - Here's what I have that pulls the entire year's data:

=SUMIFS({F-10.1.1-02 Quality Summary Nonconformity  Range 2}, {Quality Summary Range 1}, [Look Up Fields]@row, {Quality Summary Range 5}, ISDATE(@cell ), {Quality Summary Range 5}, YEAR(@cell ) = YEAR(TODAY() - DAY(TODAY()) - 1) - 1)@cell

Paul Newcome

You don't insert it anywhere. It is a stand-alone formula specifically for troubleshooting. It is most certainly not the final formula.

kmorsesmartsheet

Apologies. . . misunderstood. I get what you're trying to do now. I'll work with this next week, you've been helpful.