How to SUMPRODUCT cells that contain formulas

1
votes

So, what I need is to show SUMPRODUCT of two cell ranges? Both of these cell ranges, that is, each cell contains formula in it. From this formulas I get some number in the cells. This is the way I'm doing it right now:

=SUMPRODUCT((S7:S1000)*(T7:T1000))

and because of formulas I get error A value used in the formula if of the wrong data type

How could I solve this problem? Is there some kind of way to read just number in the cell and not the formula?

Thanks

2
excelexcel-formula
replacing "*" with comma as per chuff's reply will certainly ignore any text values (including "") returned by the formulas......but it may also ignore "numbers" formatted as text - what formulas do you have in S7:T1000? – barry houdini
What do you mean "number in the cell and not the formula", excel will evaluate formulas the do the sumproduct unless there is text as @barryhoudini states – glh

2 Answers

4
votes

Replace the "*" with a comma (",").

1
votes

I've had so much problems with this and in the end it was that instead of comma(",") I needed to use semicolon(";"). Maybe its up to Excel version, I'm using 2010?! So, solution was:

=SUMPRODUCT(S7:S1000;T7:T1000)