CODEWITHSUNDEEP
excel
user1911069
user1911069

Reputation: 51

Excel formula producing #VALUE! even though I have all numbers

Why isn't this formula working:

=IF(C2:N2>1,SUM(C2:N2-1),SUM(C2:N2))

There are no spaces, all are numbers in that range. Yet it produces the dreaded #VALUE! error. I've looked up everything and tried every possible way of doing this. I tried sumif but I can't take 1 off the cell value in that, it seems.

What I'm trying to do: If the number in the cell is larger than 1, then sum each number -1 off it.

Upvotes: 0

Views: 79

Answers (1)

Harun24hr
Harun24hr

Reputation: 36880

Use this array formula.

=SUM(IF(A1:A3>1,(A1:A3)-1,A1:A3))

enter image description here

Note: Press Ctrl+Shift+Enter after typing formula.

Upvotes: 1

Related Questions