Date and SUMIF or SUMIFS formula

Kaylynn

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...

Paul Newcome

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.

Paul Newcome

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.

Kaylynn

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

Paul Newcome

Happy to help! 👍️

Kaylynn

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.