# Sum Only Values Greater Than Zero From Multiple Worksheets

#### dcofer

##### Board Regular
Hello All,

I have a value in cell F23 in each of 24 worksheets. I can easily sum these values on a consolidation worksheet, but I only want the sum of the positive numbers. If the value is less than zero, I do not want it included in the total.

I tried the SUMIF function, but that returns a #value error. I have searched the forums and cannot find a solution that fits my specific situation.

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
What formula did you try?

Sheetlist is Named range that includes the relevant sheet names.

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheetlist&"'!F23"),">0"))

Code:
``````Function SUMRANGE(row As Long, col As Long) As Variant

For x = 1 To ThisWorkbook.Sheets.Count

If Sheets(x).Cells(row, col).Value > 0 Then
SUMRANGE = Sheets(x).Cells(row, col).Value + SUMRANGE
End If
Next x

End Function``````

Because I like VBA, here's a custom function that takes the row and column as input. It will return the sum of all values in the workbook at that location assuming it's over 0.

The formula I tried was:

=SUMIF('UNIT 1 2014:UNIT 26 2014'!F23,">0")

The first sheet is called "UNIT 1 2014" and the last sheet is called "UNIT 26 2014"

I tried the formula you provided and replaced "Sheetlist" with "UNIT 1 2014:UNIT 26 2014" but that did not work. What did I not do correctly?

Are the spaces in the Sheet Names causing a problem? If so, I can remove them. This is a Spreadsheet I inherited and I am trying to clean it up and automate some calculations.

Code:
``````Function SUMRANGE(row As Long, col As Long) As Variant

For x = 1 To ThisWorkbook.Sheets.Count

If Sheets(x).Cells(row, col).Value > 0 Then
SUMRANGE = Sheets(x).Cells(row, col).Value + SUMRANGE
End If
Next x

End Function``````

Because I like VBA, here's a custom function that takes the row and column as input. It will return the sum of all values in the workbook at that location assuming it's over 0.

Thanks, I will keep it in mind if a formula cannot be found to solve the issue. I was hoping to do this without a VBA solution, but I might have to use VBA if nothing else works.

The suggestion included the comment
"Sheetlist is Named range that includes the relevant sheet names."

Prepared a list of the relevant sheet names and name the range "Sheetlist".

The suggestion included the comment
"Sheetlist is Named range that includes the relevant sheet names."

Prepared a list of the relevant sheet names and name the range "Sheetlist".

OK, got it. Thanks. I did not catch that part of your solution.

That seems a lot of trouble, but it only has to be done once, unless I add additional sheets.

I will give it a try and let you know.

Thanks again for clarifying it for me.

Dave,

Your solution worked perfectly (once I executed it properly). Thanks for your help.

Neon, thank you as well for the time and effort you took to provide a VBA solution.

You guys are great. This saved me a lot of time each month.

Thanks again!

Thanks for the feedback.
Dave

What formula did you try?

Sheetlist is Named range that includes the relevant sheet names.

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheetlist&"'!F23"),">0"))
Hello, i am New here.. I find your post, but i don't understand how to apply. I copy formula in Excel, but where/how should i create that range of sheets? Thank you very much! Maybe you can upload a little sample of Excel?

Replies
3
Views
179
Replies
4
Views
449
Replies
19
Views
671
Replies
15
Views
570
Replies
7
Views
180

1,203,681
Messages
6,056,714
Members
444,887
Latest member
cvcc_wt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back