CODEWITHSUNDEEP
excelexcel-formula
Alan Tingey
Alan Tingey

Reputation: 971

Excel Sumproduct ignore text issues

I have the following formula which works perfectly until I have a text value in column E. When there is text in column E I get "#VALUE!"

=SUMPRODUCT(D7:D19,1/(1+E7:E19))

Any help on this would be appreciated.

Many thanks, Alan.

Upvotes: 0

Views: 144

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

use:

=SUM(D7:D19*IFERROR(1/(1+E7:E19),0))

Depending on one's version this may need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

Upvotes: 1

Related Questions