Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel Advanced - V-Lookups and Formulas
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel Advanced - V-Lookups and Formulas
Resolved · Medium Priority · Version 2007
Reply Thu 30 Jun 2011, 10:37Delegate Alex said...
Alex has attended:
Excel Advanced course
Excel Advanced - V-Lookups and Formulas
I have created a budget which has been separated into sections ie pre event work, onsite work and post event work. For each section, i have listed the time for each person in my team which they will have to spend ie the number of days they will work pre, on and post event.
I have created a VLookUp so that everytime I add a person's name, then their internal and then their external rate is included in the rate box so I'm happy with that
What i would now like to be able to do is to be able to add up all the days that 1 person would work over the whole project by just inputiting their name into a box rather than individually clicking on each box which has their name and number of days worked.
Is there a formula that does this as I've attempted a few.
Thank you
For upcoming training course dates see: Pricing & availability
RE: Excel Advanced - V-Lookups and Formulas
Hi Alex,
Thanks for your question.
If I understand the question, I think that the SUMIF should give you what you need. It has 3 parts, the last is the column of numbers you want to add up, the second is the condition you want to use and the 1st is the column the condidion applies to. The 1st and 3rd arguments must be the same length.
Suppose you type the name into cell A1 and you want the total number of days in B1. You also have the following 6 columns of information somewhere else in the spreadsheet, not necessarily beside each other: names_pre, days_pre, names_on, days_on, names_post and days_post.
the formula in B1 is:
=SUMIF(names_pre,A1,days_pre)+SUMIF(names_on,A1,days_on)+SUMIF(names_post,A1,days_post)
Hope this sorts out your summary!
Regards
Clare
Reply Thu 28 Jul 2011, 14:11Delegate Alex said...
RE: Excel Advanced - V-Lookups and Formulas
HI Claire
Thanks for your reponse. Any chance we can talk through on the phone as I don't really understand how I can put the above in to my column to get the answer I want?
Let me know the best number to call so I can call when I have time to put aside for this.
Apologies for the delay in getting this back to you
Alex
RE: Excel Advanced - V-Lookups and Formulas
Hi Alex,
Your best bet is probably to e-mail me the file, so I can have a quick look at it first - clare
. Phones are always tricky for timing due to training schedules! If you could put some sort of a comment in the spreadsheet about where you would like the answer, I'll be able to see more clearly what you are getting at, and I'll get back to you.
The delay is no problem!
Clare
RE: Excel Advanced - V-Lookups and Formulas
Hi Alex,
I made a copy of your sheet and put the sumif functions down at the bottom, where you do your additions along with a few comments. You could name various cell ranges rather than use cell references as I did.
I hope this gives you what you need.
Attached files...
Working_Budget_Wales_MD.xlsxThu 7 Jul 2011: Automatically marked as resolved.
|