Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Apr 2014
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post

    SUMIF multiple sheets

    Hi

    I need to sum 100 sheets within a specific criteria.

    Sheets - 01,02,03,...,, 99
    Range to sum (B3:L500) in all the sheets
    Criteria - when is a specific date in I2 (present in all sheets in the same cell)
    Sheet "TOTAL" need to sum all sheets where the criteria date is true.

    I created a reference name for the multiple sheets ='01:99'!$B$3:$L$500 named "DataSheets"

    I tryed =SUMPRODUCT(SUMIF(INDIRECT("'"&DataSheets&"'!I2"); I2;INDIRECT("'"&DataSheets&"'!B3:L500"))) by returns #VALUE

    What I'm doing wrong?

    Thanks in advance for any help

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Lucia,

    Here is a User Defined Function (UDF) that will sum the ranges (B3:L500) on each sheet if the dates in cells I2 (formatted as dates) are a specific date. The number of sheets makes no difference and sheet names can be anything you like, however the sheet with the Total formula must me named Total.

    In a standard module, place the following code:

    Code:
    Public Function Total(dte As Date) As Long
    Application.Volatile
    For Each sht In ThisWorkbook.Sheets
        If sht.Name <> "Total" And sht.Range("I2") = dte Then
            Total = Total + WorksheetFunction.Sum(sht.Range("B3:L500"))
        End If
    Next sht
    End Function
    On the Total sheet, in the cell that you want the total to display, enter the following formula:

    =Total(date) where date is the criteria date

    examples:
    =Total("2/15/2015")
    =Total(H2) where H2 is the criteria date

    Any change in any of the dates in cells I2 or the B3:L500 ranges are immediately reflected in the total.

    HTH,
    Maud

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    RetiredGeek (2015-12-28)

  4. #3
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,176
    Thanks
    47
    Thanked 982 Times in 912 Posts
    Very nice!

    cheers, Paul

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Thanks!!

  6. #5
    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
    You could also add a simple sum formula =SUM(B3:L500) to each sheet in say B2, then use:
    =SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(ROW(1:99),"00" )&"'!I2"),I2,INDIRECT("'"&TEXT(ROW(1:99),"00")&"'! B2")))
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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