Date and SUMIF or SUMIFS formula

Kaylynn ✭✭
edited 01/29/20 in Formulas and Functions

I could use some help as I am not savvy with formulas. I have this long formula that references another sheet to calculate production credit by "assessors." This formula works fine and it calculates the entire sheet. I currently use this formula to calculate annual production credit.

=SUMIF({Production Credits_Sheet2 Lead Coach}, Assessor1, {Production Credits_Sheet2 Lead Coach$}) + SUMIF({Production Credits_Sheet2 Inbox}, Assessor1, {Production Credits_Sheet2 Inbox$}) + SUMIF({Production Credits_Sheet2 Direct Report}, Assessor1, {Production Credits_Sheet2 Direct Report$}) + SUMIF({Production Credits_Sheet2 TeamMeetingScorer}, Assessor1, {Production Credits_Sheet2 TeamMeetingS$}) + SUMIF({Production Credits_Sheet2 TeamMeetingNonScorer}, Assessor1, {Production Credits_Sheet2 TeamMeetingNS$}) + SUMIF({Production Credits_Sheet2 LeaderMeeting}, Assessor1, {Production Credits_Sheet2 LeaderMeeting$}) + SUMIF({Production Credits_Sheet2 InterviewWriteUp}, Assessor1, {Production Credits_Sheet2 InterviewWriteUp$}) + SUMIF({Production Credits_Sheet2 ReportWriting}, Assessor1, {Production Credits_Sheet2 ReportWriting$}) + SUMIF({Production Credits_Sheet2 DevSugg}, Assessor1, {Production Credits_Sheet2 DevSugg$})

However, I would like to add to the formula to calculate just for the month of January, then February, etc...

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I will use your first SUMIF as an example, and you would update the rest of them following the same pattern (don't forget to add the "S" to make it a SUMIFS and note the different location in the formula of the range to be summed).

    =SUMIFS({Production Credits_Sheet2 Lead Coach$}, {Production Credits_Sheet2 Lead Coach}, Assessor1, {Production Credits_Sheet2 Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2020))

    Then you would update the month number from 1 to whatever month and the year number from 2020 to whatever year. You can also replace the month and year numbers with cell references if you already have those in another column somewhere else on the target sheet.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!