Sum Only Values Greater Than Zero From Multiple Worksheets

[dcofer]

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.

Thanks in advance for your help.

 

[Dave Patton]

What formula did you try?

Sheetlist is Named range that includes the relevant sheet names.

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

 

[NeonRedSharpie]

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.

 

[dcofer]

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.

Thanks for your help.

 

[dcofer]

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.

Thanks for your help.

 

[Dave Patton]

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".

 

[dcofer]

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.

 

[dcofer]

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!

 

[Dave Patton]

Glad it worked.
Thanks for the feedback.
Dave

 

[1Ionut1]

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?