Sum absolute numbers, ignore text and formulas

virtuosok

New Member
Joined
Sep 2, 2020
Messages
43
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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

virtuosok

New Member
Joined
Sep 2, 2020
Messages
43
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,114,014
Messages
5,545,497
Members
410,687
Latest member
Sunshine2020
Top