Expert: Aidan Heritage - 11/8/2009

Question
I have 20 worksheets that contain some of the same data (names).  I want the formula to look at every worksheet, look for the name on my Totals worksheet in a specific column, then sum the data on the Totals worksheet.  Names are in the 'A' column on each sheet, data to sum is in the 'B' column on each sheet.

This is what I have that's not working.

=SUMIF(SMITH:JOHNSON!A4:A200,TotalsA3,SMITH:JOHNSON!B4:B200)

Thanks!

Ray


Answer
Cannot be done directly with functions – it’s one of the annoying things about excel – you CAN do it with a UserDefinedFunction however – try


Public Function sumasheet(rar As String, valvar As Variant, offvar As Long)
For Each wk In ActiveWorkbook.Sheets
If wk.Name <> ActiveSheet.Name Then
For Each cell In wk.Range(rar) '
If cell.Value = valvar Then sumasheet = sumasheet + cell.Offset(0, offvar).Value
Next
End If
Next
End Function

(put this in a module – press Alt F11, then insert module and paste the above)

Then call it by using the function almost as any other

=sumasheet("B3:b13",B4,1)

NOTE that I’ve enclosed the actual range in quotes as this was the only way I could get the function to behave!  In this example it would sum all values in c3:c13 (one to the right of b3:b13) if the value in b3:b13 is the same as B4 on the calling sheet.