Sumifs

[avk5021]

I'm having difficulties writing a formula that would help me SUM the average job per day at my factory.

Here an example of how my raw data looks like
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Order[/TD]
[TD]total # of job

[/TD]
[TD]start date[/TD]
[TD]finish date[/TD]
[TD]average job per day[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]100
[/TD]
[TD]1 nov 17[/TD]
[TD]1 nov 17[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]200[/TD]
[TD]2 nov 17[/TD]
[TD]3 nov 17[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]400[/TD]
[TD]2 nov 17[/TD]
[TD]3 nov 17[/TD]
[TD]200[/TD]
[/TR]
</tbody>[/TABLE]

Now I would like to be able to write a formula that would give me a result in the other tap like this
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]date[/TD]
[TD]total job per day[/TD]
[/TR]
[TR]
[TD](date)[/TD]
[TD](formula)[/TD]
[/TR]
[TR]
[TD]1 nov 17[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]2 nov 17[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]3 nov 17[/TD]
[TD]300[/TD]
[/TR]
</tbody>[/TABLE]

Any help is much appreciated thanks!

 

[BrianJN1]

Re: SUMIFS help!

I'm having difficulties writing a formula that would help me SUM the average job per day at my factory.

Here an example of how my raw data looks like
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Order[/TD]
[TD]total # of job
[/TD]
[TD]start date[/TD]
[TD]finish date[/TD]
[TD]average job per day[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]100[/TD]
[TD]1 nov 17[/TD]
[TD]1 nov 17[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]200[/TD]
[TD]2 nov 17[/TD]
[TD]3 nov 17[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]400[/TD]
[TD]2 nov 17[/TD]
[TD]3 nov 17[/TD]
[TD]200[/TD]
[/TR]
</tbody>[/TABLE]

Now I would like to be able to write a formula that would give me a result in the other tap like this
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]date[/TD]
[TD]total job per day[/TD]
[/TR]
[TR]
[TD](date)[/TD]
[TD](formula)[/TD]
[/TR]
[TR]
[TD]1 nov 17[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]2 nov 17[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]3 nov 17[/TD]
[TD]300[/TD]
[/TR]
</tbody>[/TABLE]

Any help is much appreciated thanks!

So, because for Nov 1 the start and end date are the same then there is just one set of data, but as Nov2 and Nov3 have jobs that overrun one day then that data has to be reconciled across two day?

I assume therefore that if certain jobs run across "N" days then you have to recognise 'N" set of data?

 

[avk5021]

Re: SUMIFS help!

Yes, the "N" days have to be recognised as I would like to get the result for total number of jobs per day. My actual raw data is very big and it would save lots of my time instead of adding average by myself.

 

[BrianJN1]

Re: SUMIFS help!

Fine. Three more questions:
1. You would perform this operation at the end of the month?
2. How do you deal with data that overlaps the end of the month?
3. Does my table below represent a reasonable extension of what your data might represent?
(I haven't properly worked out how you calculated your "average per day jobs". Is that necessary for the presentation of your next sheet?)
I am not seeing that I can assist your further, but your replies may assist someone else -- or they may see from my endeavour something that I have missed.

Excel 2013/2016
	A	B	C	D	E
1	Order	total # of job	start date	finish date	average job per day
2	A	100	1-Nov-17	1-Nov-17	100
3	B	200	2-Nov-17	3-Nov-17	100
4	C	400	2-Nov-17	3-Nov-17	200
5	D	200	2-Nov-17	6-Nov-17
6	E	150	3-Nov-17	5-Nov-17
7	F	300	3-Nov-17	8-Nov-17
8	G	180	3-Nov-17	6-Nov-17
Sheet1