Google-sheets – Adding Positive Values Only (across multiple sheets)

formulasgoogle sheets

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?

Best Answer

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

Related Topic