-1

I have used my formula

=SUM(D8:D11)-D12-D13-D14-D15

My main data in D1 to D11. Sometimes D12, D13, D14 & D15 data will be blank. Then I will remove the above D12, D13, D14, D15 data. Then It will be #ref! error.

Are there any solutions to avoid #ref! error.

#ref! error sample is here

Forward Ed
  • 9,484
  • 3
  • 22
  • 52
Dider Elahi
  • 79
  • 1
  • 1
  • 4
  • With your screen capture, we cannot tell row or column references. Is is safe to assume that D1 is 385,373.00? – Forward Ed May 23 '16 at 13:38

1 Answers1

0

Well part of the problem you will face will is the variability of the number of rows you have. I was looking at your number columns and I made the assumption that anything with a number less than 5 in column C you are adding, and anything greater than 4 in Column C you are subtracting. In the row where you have the total and you are having your error use the following.

=SUMIF(Reference Range, Criteria, Range to Sum from)

Which for you translates to:

=SUMIF($C1:$C9,"<"&5,D1:D9)-SUMIF($C1:$C9,">=5",D1:D9)

Because I did not lock the D column I can copy this one column to the right and the D references will change to E.

The main thing this is doing is taking the sum of everything that has a value in column C less than 5 and then subtracting the sum of everything that has a value of greater than or equal to 5 in column C.

Now if the assumption about those values in column C are incorrect let us know because we could also do it for subtracting anything starting with Depreciation.

Forward Ed
  • 9,484
  • 3
  • 22
  • 52
  • Ok Sir, Thanks a lot. This formula is actual for my. But here is a little problem in other file same as my attach file. sorry I can't attach image. When in column C3 is 1 & c5 is 2, other cell will blank. I mean that C2:C9 have only two cell number & other cell may be blank. Then it count only Criteria 1 & 2. But What formula will be correct to calculate full d column – Dider Elahi May 27 '16 at 17:11
  • 1) IS the assumption about column C correct. 2) Since your screen shot cut off row and column reference letters and numbers, is the assumption that D1 has a value of 385,373.00 correct? 3) How do you distinguish which rows will be added and which rows you will subtract since not all rows will always be there. – Forward Ed May 27 '16 at 17:31