I have a workbook containing multiple sheets with a cell with a drop list of 2 choices of banks. The adjacent cell contains a dollar amount. The last sheet is to add up the dollars amounts for the first bank in 1 cell and add up the dollar amounts of the 2nd bank in another cell. I just can't figure how to add the cells when using an IF statement. But perhaps there is a better way to do it. I have attached a sheet with example.banks.xlsx
Many thanks in advance for your help and suggestions.
Last edited by lisach; 02-06-2012 at 10:16 PM.
Put this formula in E3, then copy down:
=SUM(SUMIF(INDIRECT("Sheet"&{1,2,3}&"!C4"),"="&D3,INDIRECT("Sheet"&{1,2,3}&"!D4")))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
That works incredibly well! Now since my attached sheet is just an example I need some further information (if you please?) I am trying to understand the syntax you used to reference the sheet names. My sample is using generic sheet names. My live workbook contains sheets: Mon, Tues, Wed, Thurs, Fri, Sat.
Could you explain the syntax to me so that I can apply it?
Oh and one more issue.... I know that in a formula if the name of a referenced sheet is changed the change will carry over into the formula. But this one doesn't appear to behave the same way. I ask because this is in a template that is saved under different names and the sheet names I cited above are appended with the date ie.... Mon 1-1, Tues 1-2, etc.... I know I should have mentioned this to begin with, I just didn't think it was important until I saw the effect within your solution.
It's an array formula, the example I gave you actually creates 3 separate formulas for each Sheet1, Sheet2 and Sheet3. That works because it matched your example.
Your sample workbooks need to match the working environment so as to not waste time with inaccurate suggestions. If your sheet names are not related, then I would employ a different method.
Have you dumbed the whole question down too far? Are there more cells to consider? More criteria? Working with your fuller workbook may save you (and us) time.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
I do appreciate your question. My enormous apologies. I don't do arrays and thus truly didn't understand the impact of changing the sheet names. Changing the sheet names has not been an issue for me before.
The cells I am working on are in sheet WEEKLY SUMMARY cells E6 & E7. Referencing the daily sheets Cells F21 & G21
Weekly Template for Business.xlsx
There is an array of sheetnames off to the right in column L. Those are used to create a named range MySheets, and that is then used in the array formula entered into E6, then copied down. As an array, you confirm any edits by pressing CTRL-SHIFT-ENTER to reactivate the array, and you will see the braces { } appear around the formula to indicate an active array.
I also corrected all the formulas in your other columns to properly do a 3D sum of the same cell across the sequential sheets, like =SUM(Mon:Fri!$B4),
Most of the formulas on the Summary sheet were updated in similar ways.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Thank-you Jerry, I really appreciate all the help. Apologies for not replying sooner, was gone for the weekend. I need to read up a little on this, I want to understand how this works. And it CERTAINLY DOES WORK! You just gave me a lot of studying to do!Can't thank-you enough.
Glad to help, I'm sure you'll figure it out. Post back here if you have clarification questions.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Hi. its me again. Got a problem with the formula you wrote and i wasn't able to figure out how to fix it. I attached the sheet with populated data. The formulas in cells E6 and E7 on the weekly Summary sheet aren't adding up correctly. Could you please look at it and see? thanks
Copy of Weekly Template for Business-1.xlsx
I populated cells in Fri and Sat sheets only for testing. Cells B21 and G21. Let me know if I didn't clarify enough.
Look at the formula:
=SUM(SUMIF(INDIRECT(MySheets&"!F21"), "="&D6, INDIRECT(MySheets&"!G21")))
In this structure, F21 is the cell being "checked" on each sheet, and G21 is the values being added.
Seems to me like you want A21 and B21 in the new structure.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Hi Jerry,
Thanks for taking a look at this. Sorry for the delay. Am working at the moment in location where I have no internet connectivity during the day. (ugg..)
Its not that there is a new structure. The sheet is exactly the same. The thing is, on each daily sheet there are 2 locations for deposits. One for the Self Service revenue (CELL B21) and the one for the Wash revenue (CELL G21). Accounts exist at 2 different banks. And each type of deposit, SS or W, could go to either bank (thus the drop list in cell A21 and F21. So this summary sheet is to go back and look at the daily sheets and total the amount of deposits that went to each bank.
So on the summary sheet:
Cell E6 should go back to the each daily sheet looking at cells A21 and F21..... if either = "Wells Fargo" add and total Cell B21 and G21 respectively.
Cell E7 should look at A21 and F21.... if either = "Community1" add B21 and G21 respectively.
Thereby giving a total of all deposits to 1st bank in Cell E6 and all deposits to 2nd bank in cell E7.
Perhaps i didn't make myself very clear in the beginning. My apologies in advance is so.
So, put two versions of the formula in the same cell with a + between them... should be all you need.
=SUM(SUMIF(INDIRECT(MySheets&"!F21"), "="&D6, INDIRECT(MySheets&"!G21"))) +
SUM(SUMIF(INDIRECT(MySheets&"!A21"), "="&D6, INDIRECT(MySheets&"!B21")))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
You would think so but something just doesn't work. Sorry to be such a pain.... I copied that very formula and pasted it into Cell E6 on the summary worksheet and it didn't pick up the wells fargo deposit on Friday. I copied it and pasted it into Cell E7, changed the criteria to D7 and it gives my a value error.
Copy of Weekly Template for Business-2.xlsx What did I do wrong?
You forgot that these are array formulas. When you're editing, you confirm those changes by pressing Ctrl-Shfit-Enter to reactivate the arrays, else it simply doesn't work.
You'll know the array is active when you see the curly braces { } appear around your formulas.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks