Hi all!
I am trying to add up all values from the cell above the search criteria. Although sumifs does work well, it wont search the entire sheet, it only works in specified rows.
Also, the problem is that the values to be added are determined via a vlookup based off of a cell whose value is chosen by a drop down choice.
Thank you for your help!
Last edited by mickeygoldsmith; 05-06-2009 at 05:14 PM.
A small sample spreadsheet would help to see what your case is...
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Sorry about that!
I am attaching a sample. As you can tell i have really focused my work solely on rows 2-6.
The other rows will be exactly like those rows. I think the formula used in row 3 is pretty self explanatory. Let me know. But that row is where I am having the formula problem.
Thank you!
Can anyone help me with this???
Thank you!
Can you give some further exmplanations.. i don't see what you need :-S
The coding in Row 3 is not good. For some reason, it won't let me choose the same service area twice.
What I'm trying to do is:
1) the user chooses a service area from the drop down menu in cell C6.
2) Cell C3 then checks to make sure that the total available work hours per week (cell A3) is less than the total available service area hours (total available service area hours= check to see if the service area has been chosen already. If it has, subtract it from the total amount of hours for the service area(found on sheet4))
3) if the total available work hours per week is less, then input the total available work hours per week, otherwise put in the total amount of hours for the service area available.
Does this make sense?
Does this formula in C3, copied across, do as you desire?
Please Login or Register to view this content.
Unfortunately not, as it doesn't take into account the hours that have already been used from other cells making the same selection. Would you mind taking a look at the code I put in cell C3 and C4? They are slightly different.
Thank you!
It doesn't make sense, because A3 has a text string.
Your message says:
A3 should have a number [of hours].Please Login or Register to view this content.
Can you fill in some real data and explain the logic and/or expected results?
modytrane
I apologize, I meant C1.
Have a look at the attached... I put the formula I proposed earlier in row 3.Originally Posted by mickeygoldsmith
You'll notice that the first time the option in C6 is picked it put 40 hours which is max avail time in C1., then when the option is repeated in D6, the total hours in D3 is now 27.5... which is the 67.5 hours max from your other sheet minus the 40 hours already put in in C6... I put more options in to let you see..
why, if it is, is this wrong?
I do apologize, when I tried it, it didnt work for some reason. Is there a way to implement the code for the entire page? If you look, you'll see that each employee has 3 rows. Is there a way to check to see if any employee has chosen that specific service area?
Thank you!
Do you mean you want the formula in C3, C7 and C11 for the example you attached for employee, Old McDonald?
Also, where should the formula look to see if the selection has been picked.. i.e. only if picked in rows above and rows to the left? Is that the order being filled?
I mean for all employees: Old Mcdonald, PeterPan and any other employee that I might add. And it should search everywhere and collect the hours from 3 rows above it (i.e., the number populated from your min code)
See attached.
Formula in C3:
Fomula in C7:Please Login or Register to view this content.
Formula in C11:Please Login or Register to view this content.
each copied across, then copied to other groups of 3....Please Login or Register to view this content.
Would you mind just explaining your code?
Thank you
First, is it working properly?
Actually not 100% as the code doesnt check on previous rows if the service area was chosen.
The formula in row 7 does look at the items in row 6
and so does the formula in row 11, I just neglected it to add to look at items in row 10..so change formula in row 11 to:
Please Login or Register to view this content.
Can you please explain your code?
Also, if i add 5 more "employees" (maybe Cinderella or something), i have to specify each line to look at? There is no possible code to scan the entire sheet?
If no selection was made, it will return blank.
Otherwise it will take the minimum of:
1. The Max allowance from row 1
2. The max number of hours from the table in Sheet4 minus the sum of all the Target times from the target times in the rows above where the same selection was made and the sum of all like selections in the same row the formula is in...
The formulas are for have a total of 3 Target Time rows per employee... so you just copy those formulas for the sets of 3 target times for each employee.
Ok...
If you keep formula in row 3 as is for first one...
and then use this formula in row 7:
you can then copy/paste the formula to all other names/target dates and it will search entire sheet above where the formula is for that same name....Please Login or Register to view this content.
In my attached example I only did for Old McDonald.
But i am still getting some negatives......I shouldnt be able to get negatives
Can you repost showing that.
here you go
Take another look...
sorry...it's been a busy day...and I probably shouldn't have even tackled the forum today...because I now find myself jumping back and forth...and obviously making careless mistakes...
I forgot to make a couple of cell references absolute...so wasn't copying right...
I made a slight change to the name of the service areas. And look at row 7 and 10. I made the same toy and the amount of hours available are 14 and its saying here 24. Thank you!
I have to say that I am really embarrassed about this one... I am sorry, I did forget to add back the summing of the items in the current row...
Hopefully this is the last amendment....
try now:
in C7, copied as per before to other columns/rows.Please Login or Register to view this content.
If you are satisfied with the solution provided, please mark your thread as Solved.
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
Sorry to bother you again, But cell c11 doesnt appear to be working. Please try it and get back to me!
NBVC, you helped me a lot for Old Macdonald, but how do I implement code for Peterpan so that it does the same thing, but also takes into account the choices made by old macdonald. I have to do this not only for 2 employees, but 10!!!
Please help!!
What do you mean by takes into account what Old MacDonald chose?
I mean that say you set up all three rows for Old MacDonald (you have chosen the processes and the hours have been tabulated), I want to now focus on Peter Pan and have his choices take into the choices already selected by Old MacDonald so that it is impossible to go over on the hours.
So the name of the person has nothing to do with the hours?
If Old MacDonald uses up all the hours available from Sheet4, then PeterPan entries are 0? Is that correct?
Exactly!!!
Have a look at the attached...
For added names.. you just need to copy the gray section of the "Peter Pan's group" to the gray entries of those other groups...
Note, that the formulas in Old MacDonald's group have changed too.
I filled some data in for testing....seems ok.
Ok, so obviously the test excel sheet i sent you is a whited out version of what i need. But when i plugged in the real cells into the real spreadsheet that i need, it gave me back negative numbers......any ideas?
Unless I see a better representation of the real sheet.. I wouldn't know.
Hello NBVC,
I know, it's been a long time. Using the same test spreadsheet you've attached, I am now llooking to keep a tally of every workload chosen and how many target hours have been assigned to it and how many actual hours have been assigned to it across multiple employees (Old macdonald, and peterpan and so on)
Thank you!
See columns Z to AE in the attached, Sheet Schedule.
I listed each of the Workload types as headers and then used SUMIF for each row to calculate totals.
is there a way to have it calculate those figures in another tab just showing the workload options, and the total hours assigned to it? that way in case I change the workloads(projects) i can just copy that one sumproduct or sumif statement?
Please take a look at the 'Summary' tab.
See attached.
Notice the offset ranges in the Sumproduct formulas.
The formula to get total Target Hours:
=SUMPRODUCT(--(Schedule!$C$6:$W$30=Summary!$B2),Schedule!$C$3:$W$27)
formula for total Actual hours:
=SUMPRODUCT(--(Schedule!$C$6:$W$30=Summary!$B2),Schedule!$C$5:$W$29)
Can you please explain the formula in more detail? I don't really understand it.
Thanks!
Did it do what you expected?
i think so, yes....
Well, bit difficult to explain, but...
=SUMPRODUCT(--(Schedule!$C$6:$W$30=Summary!$B2),Schedule!$C$3:$W$27)
In general, we can use Sumproduct like SUMIF to sum a range based on a criteria placed on another range. If that criteria check is True at a certain row, then the corresponding cell in the sum range is summed up.
Here is a good article on Sumproduct: http://www.xldynamic.com/source/xld.SUMPRODUCT.html
In this case, I made sure to select the criteria range from first row to check until last row to check, then I set the sum range to be offset by the number of rows up the values to sum are found (and made sure that the total size of the ranges are the same)... this makes the values to sum to be taken only in the rows that are offset from each row containing the search strings, by the same amount...
Thank you so much!
If you have a PM or email address that I can ask you a question outside the forum, i'd greatly appreciate it!
Last edited by mickeygoldsmith; 11-29-2010 at 01:06 PM.
Click my name in any of my posts and send a PM. But if the question is an excel question, you should post it directly in the forum.
it's about an error message that keeps coming up in excel when i try to save the doc...still don't pm?
No, post a new thread in the forum. You will have better luck... as I don't have a lot of time at the moment to investigate and it is against the forum rules to answer Excel questions outside the forum.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks