tough conditional summing?

[farnuckl]

i have a sheet where i am tracking data. the first column is the date and has job volumes in it. the next column is the hold column: if the job isn't done i put an "x" for that job and it is not summed in the date column(using sumif). next to that i have the completed column. here i put the date the job is completed. so there is three columns for each work day in the month.

i would like to have the corresponding job volume be summed into the total for the date specified in the completed column.

i wouldn't mind being able to combine the hold and completed columns if possible.

i have no VBA experience but wouldn't mind an excuse to learn if necessary.
anything to even get me in the right direction would be appreciated.

thanks.

 

[PaddyD]

probs re the html maker have been discussed in the 'about this board' forum - do a search.

& fwiw, your data design doesn't help! search the web for info on normalisation & first normal form.

 

[farnuckl]

ok, i got html lite to work. here it is.

kathleen, i didn't get a message from you. ???

PaddyD, i looked at articles on normalization but i dont see how they would apply for me. i have jobs a,b,c... everyday. the size of the job needs to be recorded for each day. and the status of the job needs to be recorded each day along with any change in that status. i don't see how i could simplify. this is more of a checklist than a database. i could be wrong i guess...

Book1.xls
	E	F	G	H	I	J	K
19	job	1/2/04	hold	finish	1/3/04	hold	finish
20	a	107			2
21	b	75	x	1/3	198
22	c	45			3
23	d	120			8	x
24	e	2			9
25	f	0			2
26	g	4	x		7
27	h	0			0
28	I	3			0
29	j	1			7	x
30	k	29			26
31	total	307			247
32	hold jobs		2			2
33					75	<--I want this summed into the total for 1/3.
34						This is easy enough for two days but the 30th
35						needs to check all the days before it for jobs that
36						were held and finished on the 30th.
Sheet5

 

[farnuckl]

ok, i got my act together here. Sorry. Anyone want to take a crack at it?

ray:

 

[Yogi Anand]

Hi farnuckl:

In reference to your 20 January 2004 16:36 post, please explain clearly what does your data represent, what you are trying to accomplish, and is 75 the right answer for what you are looking for, and an explanation as to why that is the correct answer.

 

[farnuckl]

ok, first of all thanks.

(The sheet is basically a checklist to track the processing of incoming jobs.)
This is a sheet that will track jobs through the production process. I receive jobs everyday that contain a number of records. The job names are always the same, their size varys from day to day. The size of the job is recorded for each day. This is job a,b,c,etc... For various reasons, sometimes a job is not done the day it is received. This job is held by putting an "x" in the "hold" column. This removes it from the total records processed for the day. When the job does get done, the finish date is recorded in the "finish" column.

HERE'S THE PROBLEM: I want to have the records from hold jobs that are finished on a later date included in the later date's total records processed for the day.

So from the example: job b from 1/2 was held. The total records for 1/2 does not include this number. job b from 1/2 was completed on 1/3. The total records on 1/3 needs to include not only the jobs received and processed on 1/3 but hold jobs finished on 1/3 as well. So the 75 held from 1/2 should be summed with the total for 1/3.
This can be done simply with the formulas I have for the two days. The problem with the methods I used it that the total records equations for 1/30 needs to look at all the previous days to see if an old job was finished on the 30th this would mean 20 formulas checking all the previous workdays..

Hope I was detailed enough.

Thanks again. ray:

 

[Yogi Anand]

Hi farnuckl:

This is based on what you have presented so far ...

y040128h1a.xls
	E	F	G	H	I	J	K
19	job	01/02/2004	hold	finish	01/03/2004	hold	finish
20	a	107			2
21	b	75	x	03-Jan	198
22	c	45			3
23	d	120			8	x
24	e	2			9
25	f	0			2
26	g	4	x		7
27	h	0			0
28	I	3			0
29	j	1			7	x
30	k	29			26
31	total	307			247
32	holdJobs		2			2
33					77
Sheet16

I have used the following formula in cell I33 ... =SUMIF(H20:H30,"1/3",F20:F30)+OFFSET(I33,-1,1)

As far as cumulative summing to the end of the month --you are the architect of the worksheet -- you may have to change the layout of the sheet as needed to facilitate the computations you need to make. Go ahead, prepare the layout, make the computations, and if you run into a problem -- post back with details and then let us take it fom there.

 

[farnuckl]

ok. sorry, but its not quite what i was looking for. i've simplified my example and added more stages to ease manual calculation to see what exactly is going on. perhaps i could just use it as is but the problem still remains: how can i make a formula like the one in H9 "hold records finished" for the 30th that wouldn't take up half my screen? is there a way to array the formula for all the days.
the formula for the 30 will look at each "finish" column for 1/30 and sum the corresponding number of records into the total records finished on the 30th.

Book1.xls
	A	B	C	D	E	F	G	H	I	J
1	product	1/2/04	hold	finish	1/3/04	hold	finish	1/4/04	hold	finish
2	A	1			1			1
3	B	1	x	1/4	1			1
4	C	1			1	x	1/4	1
5	D	1			1			1
6	daily records finished	3			3			4
7	jobs held		1			1			0
8	records held		1			1
9	hold records finished							2
10	total records finished							6
11	age (days)
12	1
13	1/3/04
14		There are 0 jobs past 1 days
online