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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

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,118,412
Messages
5,571,959
Members
412,429
Latest member
brahmaiah
Top