Formula to sum cells across multiple sheets or future sheets

Hello everyone,

I have a little problem with counting up cells on multiple and future sheets (future sheets = sheets that have not been created yet but i know the name of), so let`s get this started

The data i would like to COUNT up from each sheet are on the L row but i need to find if the data on the L row is "SAMPLE"

i know that is something with sumproduct COUNTIF and indirect but i keep getting errors since the indirect points me towards sheets that do not exist.

Is there a formula i can use or is this only vba?

Thanks,
-Alex

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Agree with Glenn regarding the sample file, but see if this will get you started.

1. Create a list of all sheet names
2. Give that list a range name
(you could name the named range dynamic to allow for added sheets)
3. put this where you want the answer to be (adjust ranges as needed)...
=SUMPRODUCT(SUMIF(INDIRECT("'"&Tabs&"'!A6:A1000"),A2,INDIRECT("'"&Tabs&"'!B6:B1000")))

This assumes that your criteris in in column A on the data sheets and the values in column B

One way, using a named Range (sheetlist), formula:
=DATA!N$1:INDEX(DATA!$N$1:$N$1000,COUNTA(DATA!$N$1:$N$1000))

and this formula:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheetlist&"'! L3:L11"),$F$7))

just add on additonal sheets as needed (or you can get excel to list All of the relevant sheets for you).

If you want to do that, too. Just ask.

LOL title said SUM, so I (incorrectly) used SUMIF, but description said COUNT, so Glenn (correctly) used COUNTIF

As an alternative to the volatile INDIRECT() function, I prefer to:

1) Put a helper cell on each tab that computes the conditional count/sum for that tab. =COUNTIF(L3:L11,DATA!F7)
2) Put a sum function with a 3D reference to add up all of the counts: =SUM('13-Mar:14-Mar'!A1).
3) To simplify adding/removing tabs, I often add a couple of blank, "dummy" tabs before and/or after the desired sum range, and set the 3D reference to include those dummy sheets. Any sheets in between the dummy tabs are included in the 3D sum and any sheets outside of the dummy sheets are not included. =SUM(start:end!A1)

This has a overview of 3D references: https://support.office.com/en-us/art...es_in_formulas

It is a named range. On the sheet that I posted, on sheet data, hit CTRL-F3. It calls up the list of named ranges. There is one "Sheetlist". That's where that formula is. It automatically feeds the list of sheets present in column N into the sumproduct formula.

Do you accept that the formulae works on the POSTED sheet of mine. Try adding another sheet. Does it still work?

It IS a charm. Thanks for the Rep.