|
|
|
|
New Member
|
|
Jan 24, 2013, 10:30 AM
|
|
how to count and sum a range of number
I have a range of numbers that contain hours worked
I need to calculate the total of 20 days worked
not every cell in the range has a value, so I will need to keep adding vaules
till I reach 20 days worked
I have tried count, countif, but I cannot make it keep checking cells for values, and stop when it has 20 values to sum
|
|
|
Software Expert
|
|
Jan 25, 2013, 12:12 PM
|
|
Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Make sure the workbook demonstrates your desired results mocked up manually, highlight the cells you're trying to automate.
|
|
|
New Member
|
|
Jan 25, 2013, 01:39 PM
|
|
Originally Posted by JBeaucaire
Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Make sure the workbook demonstrates your desired results mocked up manually, highlight the cells you're trying to automate.
Hi I have attached a file hope that it explains what I am looking for
|
|
|
Software Expert
|
|
Jan 26, 2013, 10:27 PM
|
|
Try this in T4, then copy to the right:
=SUM(OFFSET(S$6, , , , -10-COUNTIF(J6:S6, ""))) / 10
|
|
|
New Member
|
|
Jan 28, 2013, 06:40 AM
|
|
Hi
This is close to what I need, but it does not count back the 10 working days.
We have
16:s6 as the days that need to be checked for hours.
N6 does not have any hours so the formaula should count add from I6:s6
|
|
|
Software Expert
|
|
Jan 28, 2013, 08:53 AM
|
|
So try:
=SUM(OFFSET(S$6, , , , -10-COUNTIF(J6:S6, 0))) / 10
|
|
|
New Member
|
|
Jan 28, 2013, 09:18 AM
|
|
this is working for me. The issue is now how to add a new range if you need it as in my example
=SUM(OFFSET('warehouse-drivers'!S30,, -20-COUNTIF('warehouse-drivers'!C30:S30,"")))/20
the range of C30:S30 is a total of 17 days,, so I need to go to the previous month and look at an additiona 3+ days to get the total of the last 20 days worked.
the range I need to add to the formula is
'warehouse-drivers'!W5:AG5
thanks for your help with this I am much further along with your assistance.
|
|
|
Software Expert
|
|
Jan 29, 2013, 01:51 AM
|
|
Spanning raw data over multiple sheets in an incorrect data storage method. I'm not going to pursue that method with you.
I suggest you move your data storage process onto a single sheet. You're using COLUMNS in a way most people use rows since there are far more rows typically.
I would suggest you consider moving to a standard single-sheet data storage method, and then use a second sheet to draw information out of that single sheet.
|
|
|
New Member
|
|
Jan 30, 2013, 09:28 AM
|
|
Originally Posted by JBeaucaire
Spanning raw data over multiple sheets in an incorrect data storage method. I'm not going to pursue that method with you.
I suggest you move your data storage process onto a single sheet. You're using COLUMNS in a way most people use rows since there are far more rows typically.
I would suggest you consider moving to a standard single-sheet data storage method, and then use a second sheet to draw information out of that single sheet.
Hi I have changed the layout as you suggested, please let me know if
I have the format correct. I am now dealing with #ref error
|
|
|
Software Expert
|
|
Jan 31, 2013, 03:26 PM
|
|
This is how I would do this:
|
|
Question Tools |
Search this Question |
|
|
Add your answer here.
Check out some similar questions!
Geiger tube count measurement(range of alpha particles)
[ 1 Answers ]
6.There is an error in the experimental procedure in that is assumed that each particle is detected at the Geiger tube window. In actual fact, each particle could be detected anywhere throughout the length of the Geiger tube. The position would probably be different for each particle. Explain how...
If then sum count quotient?
[ 3 Answers ]
I'm a real rookie at this excel stuff. Hope someone can help. Here's what I need... in cells A1,B1,C1,D1,E1 a 1,2, or 3 will be entered in each. I need cell F1 to tell me which number occurred 3 times or more within those cells. If a # didn't occur more than three times I need a 0 displayed in...
View more questions
Search
|