This is my previous problem and it work: http://www.excelforum.com/excel-form...-argument.html
However, when I try to sum the data of range A1:A5, it only show the sum on first sheet. But when I check the formula with F9 it did bring all data over. Below is the formula I use, any idea?
=SUMIF(INDIRECT("'"&'Summary'!A1:A20&"'!A1:A5"),"<1000")
Last edited by jackgan; 05-12-2014 at 10:50 AM.
You need an additional function to enclose that as per Tony's post #9 in the thread you linked.
This formula:
=SUMIF(INDIRECT("'"&'Summary'!A1:A20&"'!A1:A5"),"<1000")
will return the array of the answers from each sheet, so you need to enclosure it within another function to add up the results, i.e.:
=SUMPRODUCT(SUMIF(INDIRECT("'"&'Summary'!A1:A20&"'!A1:A5"),"<1000"))
Thanks, it now did sum the data from multiple sheet. But just the value on A1 instead of A1:A5.
Take a look at the example
Here's a small sample file that demonstrates this:
SUMIF across sheets.xlsx
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Now I understand. Can you show me also if I just want to SUM, MIN, MAX, AVERAGE without any criteria?
Without any conditions you can just do a normal SUM, MIN, MAX, AVERAGE:
=SUM(Sheet2:Sheet5!A1:A5)
=MIN(Sheet2:Sheet5!A1:A5)
=MAX(Sheet2:Sheet5!A1:A5)
=AVERAGE(Sheet2:Sheet5!A1:A5)
What if I will change the sheet name later? The sheet name might be rename without number in it.
The formula(s) will automatically update to include the new sheet name.
Is that mean if I have multiple sheet with non-consistent name then I must start with the consistent sheet name and rename it later? Also can you show me to do the calculation using INDIRECT, if possible? Thanks by the way!
Here's another way that should make things easier for you.
Create a "sheet sandwich".
Insert a new sheet to the immediate left of the first sheet
you want to include in the calculation. Name this sheet Start.
Insert a new sheet to the immediate right of the last sheet
you want to include in the calculation. Name this sheet End.
Let's assume you want to sum cell A1 on each sheet. The
formula would be:
=SUM(Start:End!A1)
Each sheet that is physically located between the Start sheet and the End sheet (inclusive) will be included in the calculation.
\Sheet1/\Sheet2/\Start/\Sheet3/\Sheet4/\Sheet5/\End/
When you add new sheets that need to be included in the
calculation just put them between the Start sheet and the
End sheet.
Genius!!! That's exactly what I need!!! Thanks Tony and ragulduy for the help!
You're welcome. We appreciate the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks