Help finding the error in my formula

Question

I can't find the error in the following formula.

=IF(SUM('March 2026'!AE7:AF7,B7:D7-('March 2026'!AE19:AF19,B19:D19))>40,SUM('March 2026'!AE7:AF7,B7:D7-('March 2026'!AE19:AF19,B19:D19))-40,0)

Answer 1

Hi @Lydia Dice,

Thank you for posting your question in the Microsoft Q&A forum.

According to your concern, it's a syntax error since you can't subtract a union of range inside a SUM function. Based on your sharing, I understand your intention is calculating (Sum of AE7:AF7 + B7:D7) - (Sum of AE19:AF19 + B19:D19). Then if that result is greater than 40, return the result minus 40 and return 0 if it not.

If I understand correctly, you can use this formula instead: =IF(SUM('March 2026'!AE7:AF7, B7:D7) - SUM('March 2026'!AE19:AF19, B19:D19)>40,SUM('March 2026'!AE7:AF7, B7:D7) - SUM('March 2026'!AE19:AF19, B19:D19) - 40, 0)

If not, please share more details about your goal and your data structure so I can clearly understand the situation and provide the most appropriate guidance for your concern.

Please understand that the initial response does not always resolve the issue immediately. Please try the formula and let me know if it works for you.

If you have any questions or need further assistance, please feel free to share them in the comments on this post so I can continue to support you.

Thank you for your understanding and cooperation. I'm looking forward to your reply.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.