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 a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

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,019
Messages
5,545,531
Members
410,689
Latest member
ConfuzzledThomas
Top