Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am doing a summary table which references another page with VLOOKUP formula responses.

Currently using =SUM('CURRENT'!$J:$J) to calculate the total numerical values in column J, however there are some values which are #N/A as there is no source data.

This then returns no sum total calculations.

I was wondering the best way to incorporate the IF formula into a SUM formula

share|improve this question

1 Answer 1

up vote 3 down vote accepted

You should be able to persuade Excel to ignore the #N/As and hence get the expected result with =SUMIF('CURRENT'!J:J,"<>#N/A").

The syntax is =SUMIF(range,criteria,sum_range) but here the sum_range is the range so need not be entered ("If this range is omitted, the first range is summed instead.")

The criteria "<>#N/A" is that the summation should only apply to cells where their content is not equal (<>) to #N/A.

share|improve this answer
    
Switching from $J:$J to J:J was my mistake - but it does not seem likely to me that you would need a formula with a fixed reference to a column total. –  pnuts Apr 18 '13 at 19:24
    
Hi pnuts, i tried this but with no luck, it still returns N/A has the overall calculation. Does it have something to do with the fact that the #N/A value (and all other values in column J) are the result of a VLOOKUP formula –  Rohan F Apr 18 '13 at 23:33
    
It still returns #N/A as the total –  Rohan F Apr 19 '13 at 6:05
    
Hi pnuts, i deleted the whole formula and started from scratch and this time it worked. –  Rohan F Apr 19 '13 at 6:15
    
I think you eluded to the problem in your 1st response. I was summing all of column J, which happened to have 2 heading rows. When i changed thew formula to J3:J450 it resolved. –  Rohan F Apr 21 '13 at 23:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.