• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Is it possible for this SUMIF formula to work across multiple tabs?

S

Sami

New Member
Hi,

I am trying to figure out if it is possible to get this SUMIFS formula to work accross multiple Sheets in the same Excel file. I am creating a data summary table for courses within my school and would like to report enroment data across the four different departments according to specific rules.

=SUMIFS(M4:M39,A4:A39, "7", D4:D39, "1", F4:F39, "Long")

M4:M39 = The Column numbers that need to be added
A4:A39, "7" = Only selecting lines where the number "7" is present (SSA number)
D4:D39, "1" = Only selecting lines where the number "1" is present (Level of course)
F4:F39, "Long" = Only selecting lines where the word "Long" is present (Length of course)

The SUMIFS formula works perfectly for a single sheet, but not accross all the sheets in the file. I have tried lots of different ideas after reading various posts but have been unable to get it to work. Once I understand the basis of how this could work accross multiple sheets, I can then amend the formula to further expand on what I am trying to achieve.

I have attached a sample file. All the Cells are the same positin in each sheet.

Would be great if someone is able to provide a useful suggestion as I have spent the last week trying to resolve this.

Many thanks

Sami
 

Attachments

  • SAMPLE Course Tracker.xlsx
    41.4 KB · Views: 7
As you've guessed, SUMIFS can't work across multiple sheets. However, you can put the SUMIFS all each worksheets, and then use a 3D sum in your summary worksheet. I recommend using some "bookend" dummy worksheets with that, so that you can always add/delete worksheets w/o messing up the 3D formula. See attached.
 

Attachments

  • SUMIFS Course Tracker.xlsx
    43.8 KB · Views: 17
Many thanks for such a quick response Luke. This is a very direct way of resolving the issue.

I was also considering using the SUMPRODUCT Function so not to have to summarise the data on each sheet, but only on the new summary sheet at the end. Do you know if that is actually possible (although I have a feeling it may be out of my level of ability as have seen lots of suggested coding and it is somewhat confusing).

Either way I already have a workable solution.

Many thanks

Sami
 
Hi Luke, tried your suggestion and it worked perfectly on the first sheet. When I went to copy it all over to the second sheet, this error came up! Any suggestions? Ps I have created a new thread for this, but thought I would just update you as well.

'Excel cannot complete this task with the available resources. Choose less data or close other applications'

Do you know why this is happening and how I can overcome this issue?

Many Thanks

Sami
 
Yikes! What do you mean by "copy it all over" to second sheet? There should just be one formula (a SUMIF) on each sheet, and then one 3D summation formula on summary sheet.
 
Well each of the cells in the first sheet has the SUMIF formula relating to the data it is meant to calculate from the data table above. Then I was trying to copy the range of SUMIF formulas to each of the 4 sheets and create one summary sheet on the last sheet. Is this wrong? What is 3D summation?

Sorry for lack of knowledge, relatively new to using excel to this level.

Appreciate your help.

Many thanks

Sami
 
Good day Sami
Can you not use the formula supplied by Luke M and the just use cell references in you summary sheet.


Sorry for lack of knowledge
Click to expand...
do not apologize, we all start somewhere.


.
 
3D (three dimensial) functions are functions that can work on multiple worksheets. You can think of rows and columns as the normal 2 dimensions, and the third dimension goes "through" the worksheet, as if they were a stack of papers on top of each other. So, the 3D sum is going through all the worksheets, and add's up the SUMIFS on the other sheets.

So, let's say on one of your data worksheets you have a SUMIFS formula in cells B5, C5, and D5. You need these on every data worksheet, so you do a regular copy and paste, pasting into cells B5:D5 on each worksheet respectively. In summary worksheet, the 3D formulas for each SUMIF would be like:
=SUM('First:Last'!B5)
=SUM('First:Last'!C5)
=SUM('First:Last'!D5)

Hope that makes more sense.
 
Thank you Bob and Luke.

Luke, the 3D suggestion is great and will work perfectly on the summary sheet. Thank you for the very clear explanation as that has really helped.

My continued issue is now not being able to copy the SUMIFS formula from the first worksheet to the other 3 before I can then summarise the data in the final summary sheet. If there was a way of getting SUMIF to work across all the worksheets that would solve my problem.

Many thanks

Sami
 
You must log in or register to reply here.
Back
Top