Sumproduct returning zero - solved!

[BelleH123]

Hello, I am trying to get a formula to calculate the total square m based on a particular rate.
In K2 and K3 I have written a Sumproduct formula but it's not working. Can anyone help?


Thanks,

[joeu2004]

I'm not sure I understand what you want to calculate. But I can tell you why your SUMPRODUCT returns zero.

Change =IF(F2<2.5,"137.60","165.25") in D2 to =IF(F2<2.5,137.60,165.25). Do the same for all of column D.

Probably do the same in column E, although that does not seem to be referenced in your examples.

In general, do not put quotes around numbers that expect to use as numbers.

You get lucky with your text values in columns B and C because Excel converts them to numeric values in arithmetic expressions of the form =(B2*C2)/1000000 in F2.

But Excel does not convert text to numeric values in comparisons of the form =SUMPRODUCT(--(D2:D26=137.6),F2:F26) in K2.

Arguably, you could change the formula in K2 to =SUMPRODUCT(--(D2:D26="137.60"),F2:F26). But I think that would be "bad practice".

BTW, if the values in columns B and C should be numeric, if only for "good practice", change the formulas to =--LEFT(A2,FIND("x",A2,1)-1) and =--RIGHT(A2,LEN(A2)-FIND("x",A2,1)-1) respectively.

The double negate converts numeric text to numeric values. There is nothing sacrosanct about a double negate. Any idempotent arithmetic operation would work; for example, add zero or multiply by 1.

If that does not fix your calculations, perhaps you can explain the intent of the SUMPRODUCT expressions more completely.

[FlameRetired]

Administrative Note:

BelleH123 welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[BelleH123]

Thank-you so much for your clear instructions. This has helped solve my formula... i needed to convert the text to numeric numbers so thanks for being so helpful.

[FlameRetired]

BelleH123 please don't ignore Moderator requests.

You still need to change your thread title.