This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(284.8, 53%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECD%3C/text%3E%3C/svg%3E)
Sumproduct function is given as result 0, when the result should be 46,25. the inputs are in number format, and they come from formulas.
Note: If I do it the long way with D5D4+E5E4.... etc= the result is ok (46,25)
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Why it does not show 0 in I5?
Maybe the result is corect, but there is a problem with the formatting.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 53%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
Are your values actually numbers and not text strings?
@Barry Schwarz Yes, doing it the long way works okay
What did you change to show 0 now?
@Viorel just the format to number, it had a custom format where if the value was 0, it will show as "-"
The fact that you can simulate SUMPRODUCT with the normal arithmetic operators gives you NO CLUE whether the values are numeric or text. The arithmetic operators will silently convert a text value (like "25" or '25) to its numeric equivalent (25) before performing the arithmetic operation.
SUMPRODUCT does not perform this conversion. A text value in any of the array cells will be treated as 0 for that operation.
You can test this by temporarily replacing D6 with =SUM(D4:D5) and copying the formula through H6
Alternately you can put the workbook on a web site we can access so we can download it and perform tests.
This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Comments have been turned off. Learn more
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(201.6, 3%, 29%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EI%3C/text%3E%3C/svg%3E)
Hi,
Maybe some of your data comes out as text or some cell where you have data is formatted as text. In this case it would be better to use this formula:
=SUMAPRODUCTO(--D4:H4; --D5:H5)
or this formula:
=SUMA(BYCOL(--D4:H5; PRODUCT))
Hope this helps.
IlirU