Forum Discussion

Sue Smith's avatar
Sue Smith
Copper Contributor
Aug 23, 2017

Sum formula based on blank cells

I am trying to aggregate monthly percentages from different worksheets. If a numerator is 0, but there is still a denominator listed, I do not want to include the denominator in my calculation. (i.e. 4/6, 2/4, 0/8, 7/9 - I want to add 2, 4, 7 and 6,4,9) I have figured out the numerator formula, but am having difficulty on the denominator. Does anyone have any suggestions?

 

Thanks!

Formulas & Functions
Need Help

1 Reply

Sort By
  • IngeborgHawighorst's avatar
    IngeborgHawighorst
    MVP
    Aug 24, 2017

    Hello,

     

    could you provide a data sample? Even a screenshot would help. It is not clear how your data is organised. If numerator and denominator are in separate cells, you can use Sumif. In the screenshot below, the formula in cell A7 is 

     

    =SUMIF(A2:A5,"<>0")

     

    although, since you want to ignore Zero, you might as well do a simple sum. 

     

    The formula in B7 sums only the values in B2:B5 where the respective cell in A2:A5 is not zero:

     

    =SUMIF(A2:A5,"<>0",B2:B5)

     

     

    Does that get you any further?

     

Resources