Sum absolute numbers, ignore text and formulas

virtuosok

Board Regular
Joined
Sep 2, 2020
Messages
72
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I have a range G5:AH5 which contains a mix of regular numbers, negative numbers, text, blanks and formulas.
Am trying to get a sum of numbers only, ignoring everything else. The following
=SUMPRODUCT(ABS(G5:AH5))
{=SUM(ABS(G5:AH5))}
...both return the #VALUE! error
Any ideas please to avoid helper columns....
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

virtuosok

Board Regular
Joined
Sep 2, 2020
Messages
72
Office Version
  1. 2016
Platform
  1. Windows
Figured this out
=SUM(IF(ISNUMBER(G5:AH5),ABS(G5:AH5)))
 

Watch MrExcel Video

Forum statistics

Threads
1,119,116
Messages
5,576,194
Members
412,705
Latest member
Collie
Top