# Thread: SUMIF multiple sheets

1. ## 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. 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. Very nice!

cheers, Paul

5. Thanks!!

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

#### Posting Permissions

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