Date and SUMIF or SUMIFS formula

KaylynnKaylynn
edited 01/29/20 in Formulas and Functions
01/29/20 Edited 01/29/20
Accepted

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 NewcomePaul Newcome ✭✭✭✭✭
    Accepted 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.

    thinkspi.com

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted 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.

    thinkspi.com

  • Thank you Paul! I so appreciate you taking the time to help me!!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! 👍️

    thinkspi.com

  • Hi Paul. I posted another formula question on here and hope that you could help me. Not sure how to tag you on my post though.

Sign In or Register to comment.