0

The example formula below works perfectly in my spreadsheet if ALL the numbers are positive. However, I would like to pull the values from the cells indicated below (from 14 different pages), but ONLY if they are positive. How would I do that?

=sum(Sunday1B!M175,'Sunday 1D'!M175,Saturday1B!M175,'Saturday 1D'!M175,MondayL1!M175,Monday1!M175,TuesdayL1!M175,Tuesday1!M175,WednesdayL1!M175,Wednesday1!M175,FridayL1!M175,Friday1!M175,ThursdayL2!M175,Thursday2!M175)

What changes should I make to the formula above if I want to only add positive values?

1 Answer 1

0

First you need to reference those cells as a range. E.g. select A1 and add =Sunday1B!M175, then select A2 and put ='Sunday 1D'!M175 and so on. The result will look like this:

=Sunday1B!M175
='Sunday 1D'!M175
=Saturday1B!M175
='Saturday 1D'!M175
=MondayL1!M175
=Monday1!M175
=TuesdayL1!M175
=Tuesday1!M175
=WednesdayL1!M175
=Wednesday1!M175
=FridayL1!M175
=Friday1!M175
=ThursdayL2!M175
=Thursday2!M175

Then select A15 and add the SUMIF function:

=Sunday1B!M175
='Sunday 1D'!M175
=Saturday1B!M175
='Saturday 1D'!M175
=MondayL1!M175
=Monday1!M175
=TuesdayL1!M175
=Tuesday1!M175
=WednesdayL1!M175
=Wednesday1!M175
=FridayL1!M175
=Friday1!M175
=ThursdayL2!M175
=Thursday2!M175
=SUMIF(A1:A14,">0")

As mentioned in the formula, the SUMIF function will sum all cells in the range A1:A14 whose values match the criteria ">0" which means "greater than zero" (i.e. positive). sample range A1:A14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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