# Thread: SumIF Accross Sheets (97/2000)

1. ## SumIF Accross Sheets (97/2000)

Is there a way to use SumIf Across multiple sheets?

2. ## 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?

3. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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.

11. ## 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
•