sumproduct error

Question

=SUMPRODUCT((M1:HU1="hour")*(M5:HU5))

its giving me a value error and I cant figure out why. Is the a better way? It works on another sheet.

=SUMPRODUCT(($K$1:$CJ$1="out")*(K5:CJ5))

Answer 1

Dear @Karl Heine,
Thank you for posting your question in the Microsoft Q&A forum.

From what you described, I understand that you’re using SUMPRODUCT to add values only for columns where the header equals “hour”, but the formula returns a #VALUE! error on this sheet, even though a similar formula works correctly on another one.

This usually happens when SUMPRODUCT encounters at least one non‑numeric value in the range being summed (for example, text, a formula returning an empty result "", numbers stored as text, or an error). SUMPRODUCT does not automatically treat those values as zero, so a single non‑numeric cell can cause the entire formula to return #VALUE!. This also explains why the formula works on another sheet where the data is fully numeric.

For reference: How to correct a #VALUE! error in the SUMPRODUCT function

To help confirm, could you please check:

• Are there any cells in M5:HU5 that contain text, spaces, or formulas returning ""?
• Are all values in that range real numbers (not numbers stored as text)?
• Do any cells contain error values?

In the meantime, I recommend adjusting the formula to safely handle non‑numeric values. You can try this version:

=SUMPRODUCT((M1:HU1="hour")*N(M5:HU5))

The N( ) function converts:

• numbers > numbers
• text or empty strings > 0

This allows SUMPRODUCT to complete the calculation even if some cells are not numeric.
If error values are possible, this version is also safe:

=SUMPRODUCT((M1:HU1="hour")*IFERROR(M5:HU5,0))

Please understand that our initial response does not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.

I truly appreciate your patience and understanding. If you have any further questions or need further clarification, please feel free to reach out. I'm looking forward to hearing from you. 

Thank you for your cooperation.

---

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.