Thread: Excel formula calculation sometimes gives errors

I have a simple formula based excel (attached), Where columns :-
O will have value of column B, if it is the last occurrence of the value.
=IF(ISNA(VLOOKUP(B35,B36:D$5629,1,0)),B35,"")
P will have sum of column N, Where column B matches Column O.
=SUMIF($B$3:N35,O35,$N$3:N35)
Q will have sum of column M, Where column B matches Column O.
=SUMIF($B$3:N35,O35,$M$3:M35)


So As expected the rows 10 to 31 have values of P, Q as 0, wherever O is null, but why is row 35, 37, 43 and later showing up values in Column Q, when it should be 0 ? And even if it is showing up, why 4, 6, 8 ... why not the actual SUM ?

Please tell me the reason for this problem, since this excel issue is irritating me a lot. This issue just vanished when I put some value in H32:H39 , but why ?

Dear Mr. Bannerjee,

See the Column Q in the attached file and confirm back if this is what you were looking for.

I see a some improvement areas in your spreadsheet and will be happy to improve if you share some insight about it. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

Thanks, Mr Dilip, this formula does help, since the values become zero again.

But I was looking for the reason why they were having a non zero value at all and values less than 10, the sum (if it was) should have exceeded 1000 at any cost.

I can't yet figure out why, but your formula is counting the number of empty cells in column H. I suspect it has something to do with the fact that your sum range is within your criteria range. You will see when adding something in one of the empty values in col H that the value in row 35 changes from 4 to 3

EDIT you might also want to replace =IF(ISNA(VLOOKUP(B35,B36:D$5629,1,0)),B35,"") with =IF(countif(B36:B$5629,B35)=0,B35," ")

Dear Banerjee,

I would say once again and as shown by arthurbr, there are some opportunities to upgrade your worksheet with better formulas if you can share some details / logics which you want to apply. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

Also as you have asked that why the SUMIF is not working, below explanation my help:-

SumIf syntax:-
SUMIF(range, criteria, [sum_range])

Details regarding sum_range :-

The sum_range argument does not have to be the same size and shape as the range argument. The actual cells that are added are determined by using theupper leftmost cell in the sum_range argument as the beginning cell, and then including cells that correspond in size and shape to the range argument.
For example:-
If range is And sum_range is Then the actual cells are
A1:A5 B1:B5 B1:B5
A1:A5 B1:B3 B1:B5
A1:B4 C1:D4 C1:D4
A1:B4 C1:C2 C1:D4

So, as you are using 13 columns in your range, then your sum_range also moved accordingly.

Regards,
DILIPandey

<click on below 'star' if this helps>

The sum_range argument does not have to be the same size and shape as the range argument.

That's true, but incurs the penalty of making the formula volatile.

ya... you are correct shg..
there are still some pain left but it may be a way to use it more dynamically.. Cheers

Regards,
DILIPandey

<click on below 'star' if this helps>