Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Jan 2001
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SumIF Accross Sheets (97/2000)

    Is there a way to use SumIf Across multiple sheets?

    Thanks in advance

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: SumIF Accross Sheets (97/2000)

    This is cool, Hans. Would it be safer to set the Function return data type as Double or even Decimal?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIF Accross Sheets (97/2000)

    That's right John! I just copied my code of the CountifAcrossSheets function that I posted some time ago and forgot to change the integer data type to Double.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIF Accross Sheets (97/2000)

    I don't think this is possible without using VBA code.
    Below you can find the code for a User Defined function that allows you to sum accross worksheets using a criterion:
    (Note that you have to enter the ranges as strings in this function!!)

    <pre>Function SumIfAcrossSheets(CriterionRange As String, Criterion As String, SumRange As String) As Double
    Dim oSheet As Worksheet
    Dim Rcrit As Range
    Dim Rsum As Range
    Dim i As Integer
    Application.Volatile
    SumIfAcrossSheets = 0
    For Each oSheet In ActiveWorkbook.Worksheets
    Set Rcrit = oSheet.Range(CriterionRange)
    Set Rsum = oSheet.Range(SumRange)
    For i = 1 To Rcrit.Cells.Count
    If Evaluate(Str$(Rcrit.Cells(i).Value) & Criterion) Then
    SumIfAcrossSheets = SumIfAcrossSheets + Rsum.Cells(i).Value
    End If
    Next
    Next
    End Function
    </pre>


  5. #5
    Lounger
    Join Date
    Jan 2001
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIF Accross Sheets (97/2000)

    It appears that the function assumes there will only be numbers in the CriterionRange? Is this correct, or am I missing something?

    Also, if you don't mind telling a brain dead person, I can't seem to get to use the function without resorting to using =FileName.XLS!SumIfAcrossSHeets instead of just =SumIfAcrossSheets. I thought XLStart took care of that.

    Thank you kindly

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIF Accross Sheets (97/2000)

    I don't know how you can sum other things than numbers. The outcome of the function is defined as a Double' variable type, which indeed assumes that the data are numbers.
    You should put this function in your personal.xls to have it available all the time.

  7. #7
    Lounger
    Join Date
    Jan 2001
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIF Accross Sheets (97/2000)

    Thanks for responding.

    I'm not talking about SumRange, I'm talking about the CriterionRange, if I look for a number as my Criteria, the function works fine, if I look for a text value, say a Dept Name, the Function returns #Value!

    I know the sums the values in SumRange, and it should only have numbers in the range, but if I have any text values in my CriterionRange, it doesn't work. Is there something I need to create for the criteria?

    For example, if in Cell A1 and A5 I have Accounting, and in B1 1000, and I use Accounting as My Criteria, I get #Value!. However If I replace Accounting with say a code of 5, then the function works as long as there is no other text in my CriterionRange.

    This is what I mean.
    (Sum across the sheets if you find "accounting" in this range, then add the cells over in this range. - just like the built-in SumIf)

    Thanks again

  8. #8
    Lounger
    Join Date
    Jan 2001
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIF Accross Sheets (97/2000)

    One last question. Is there a way to not have to put Personal.xls!SumIfAcrossSheets to use the function when it is stored in Personal.xls, and simply use SumIfAcrossSheets instead?

    Thank you kindly

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIF Accross Sheets (97/2000)

    It has to do with the 'Evaluate' command that is not working in that case. I don't know why and I made some modifications which are probably not optimal, but this should work in case you use text in the criterionrange and you want to compare it using either ' = ' or ' <> '.

    <pre>Function SumAcrossSheets(CriterionRange As String, _
    Criterion As String, SumRange As String) As Double
    Dim oSheet As Worksheet
    Dim Rcrit As Range
    Dim Rsum As Range
    Dim i As Integer
    Dim sQ As String
    Dim Crit As String
    Application.Volatile
    SumAcrossSheets = 0
    For Each oSheet In ActiveWorkbook.Worksheets
    Set Rcrit = oSheet.Range(CriterionRange)
    Set Rsum = oSheet.Range(SumRange)
    For i = 1 To Rcrit.Cells.Count
    If VarType(Rcrit.Cells(i).Value) <> vbString Then
    sQ = Trim$(Str$(Rcrit.Cells(i).Value))
    If Evaluate(sQ & Criterion) Then
    SumAcrossSheets = SumAcrossSheets + Rsum.Cells(i).Value
    End If
    Else
    sQ = Rcrit.Cells(i).Value
    If Left(Criterion, 1) = "=" Then
    Crit = Right(Criterion, Len(Criterion) - 1)
    If sQ = Crit Then
    SumAcrossSheets = SumAcrossSheets + Rsum.Cells(i).Value
    End If
    End If
    If Left(Criterion, 2) = "<>" Then
    Crit = Right(Criterion, Len(Criterion) - 2)
    If sQ <> Crit Then
    SumAcrossSheets = SumAcrossSheets + Rsum.Cells(i).Value
    End If
    End If
    End If
    Next i
    Next
    End Function
    </pre>


    You can put this function in a workbook and save the workbook as an add-in. There must be a quite recent post dealing with that.
    Hope this helps.

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: SumIF Accross Sheets (97/2000)

    Hi Hans,
    I think this would work and it will allow you to use ranges rather than strings:
    Function SumAcrossSheets(CriterionRange As Range, Criterion, SumRange As Range)
    Dim oSheet As Worksheet, strCrit As String, strValues As String, strSheet As String
    Application.Volatile
    SumAcrossSheets = 0
    For Each oSheet In ActiveWorkbook.Worksheets
    strSheet = oSheet.Name
    strCrit = "'" & strSheet & "'!" & CriterionRange.Address(False, False, xlA1, , "A1")
    strValues = "'" & strSheet & "'!" & SumRange.Address(False, False, xlA1, , "A1")
    SumAcrossSheets = SumAcrossSheets + Application.WorksheetFunction.SumIf(Range(strCrit) , Criterion, Range(strValues))
    Next oSheet
    End Function
    It's not perfect by any stretch of the imagination - it does all sheets rather than giving a choice for example - but I'm still working on a better version! (You'd think Excel would be able to handle 3D ranges better by now)
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIF Accross Sheets (97/2000)

    Of course, Rory, as usual the most elegant and appropriate solution is the most simple one, using the SUMIF worksheetfunction on each sheet. Must have thought of it from the beginning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •