Divide two sumifs formula?

[njsutorius]

Hello,

I have a sumifs equation i need help with. I have four columns, dates, id#, time, and calls.

I have a sum ifs that check for the id and between a specific date range then it sums the time together, and then another equation that checks if its the same date range and id # then it adds the numbers. I want it to do this but also divide one by the other which will give me calls per hour.

It would look something like this.

Cell G1 would show the call per hour of Agent 200 by adding up all of column C if its within a specific date range and matches agent id 200 and then divide it by the sum of column D if again it matches id 200 and the same date range as before.

	A	b	c	d	e	f	g	h
1	Date	ID	Time	Calls		Employee	Calls per hour	Date Range
2	1/1/2014	200	20:20:00	200		200		1/1/2014
3	2/1/2014	201	31:21:20	300		201		3/1/2014
4	3/1/2014	200	22:22:22	300
5	4/1/2014	201	45:45:00	200
6	5/1/2014	200	30:30:00	200

<tbody>
</tbody>

Thanks for the support! great site!

 

[njsutorius]

Still getting the same result. I was able to to change the E10 in both sections. Im missing something. Trying to think it out.

=SUMPRODUCT(--(SUMPRODUCT(--('Production data'!B2:B11=E10),'Production data'!E2:E11)/(SUMPRODUCT(--('Production data'!B2:B11=E10),--('Production data'!A2:A11>=F5),--('Production data'!A2:A11<=F6),'Production data'!F2:F11))))

 

[vogel997]

Still getting the same result. I was able to to change the E10 in both sections. Im missing something. Trying to think it out.

=SUMPRODUCT(--(SUMPRODUCT(--('Production data'!B2:B11=E10),'Production data'!E2:E11)/(SUMPRODUCT(--('Production data'!B2:B11=E10),--('Production data'!A2:A11>=F5),--('Production data'!A2:A11<=F6),'Production data'!F2:F11))))

copy exactly as is, too many "()" and one too many sumproduct

=SUMPRODUCT(--('Production data'!B2:B11=E10),'Production data'!E2:E11)/(SUMPRODUCT(--('Production data'!B2:B11=E10),--('Production data'!A2:A11>=F5),--('Production data'!A2:A11<=F6),'Production data'!F2:F11))

<colgroup><col width="757" style="width: 568pt; mso-width-source: userset; mso-width-alt: 27684;"> <tbody> </tbody>

 

[vogel997]

one more advise, try to avoid merging cells.

<colgroup><col width="757" style="width: 568pt; mso-width-source: userset; mso-width-alt: 27684;"> <tbody> </tbody>

 

[njsutorius]

Your the man! thank you for all the time you spent helping me. It was very kind of you. To follow up on your previous comment that they have to be the same ranges. Is it ok to do the entire column such as $A:$A and have the ranges match like that?

 

[njsutorius]

one more advise, try to avoid merging cells.

<tbody>
</tbody>

Ya that was a bit of a mix up. Im going to redo that.

 

[vogel997]

Your the man! thank you for all the time you spent helping me. It was very kind of you. To follow up on your previous comment that they have to be the same ranges. Is it ok to do the entire column such as $A:$A and have the ranges match like that?

=SUMPRODUCT(--('Production data'!B:B=E10),'Production data'!E:E)/(SUMPRODUCT(--('Production data'!B:B=E10),--('Production data'!A:A>=F5),--('Production data'!A:A<=F6),'Production data'!F:F))

It works, but it takes much longer to calculate….

<colgroup><col width="757" style="width: 568pt; mso-width-source: userset; mso-width-alt: 27684;"> <tbody> </tbody>

 

[Aladin Akyurek]

=SUMPRODUCT(--('Production data'!B:B=E10),'Production data'!E:E)/(SUMPRODUCT(--('Production data'!B:B=E10),--('Production data'!A:A>=F5),--('Production data'!A:A<=F6),'Production data'!F:F))

It works, but it takes much longer to calculate….

<TBODY>
</TBODY>

The OP has never provided a manully calculated figure... The above formula can be rewritten as:

=SUMIF(
  'Production data'!B:B,E10,
  'Production data'!E:E)/
 SUMIFS(
  'Production data'!F:F,
  'Production data'!B:B,E10,
  'Production data'!A:A,">="&F5,
  'Production data'!A:A,"<="F6)

 

[vogel997]

The OP has never provided a manully calculated figure... The above formula can be rewritten as:

=SUMIF(
  'Production data'!B:B,E10,
  'Production data'!E:E)/
 SUMIFS(
  'Production data'!F:F,
  'Production data'!B:B,E10,
  'Production data'!A:A,">="&F5,
  'Production data'!A:A,"<="F6)

this formula works as well, just need to add the "&" before "F6" for the last criteria.

<colgroup><col width="757" style="width: 568pt; mso-width-source: userset; mso-width-alt: 27684;"> <tbody> </tbody>

 

[Aladin Akyurek]

this formula works as well, just need to add the "&" before "F6" for the last criteria.

<tbody>
</tbody>

Right. It should be way faster though...

 

[njsutorius]

this formula works as well, just need to add the "&" before "F6" for the last criteria.

<tbody>
</tbody>

Wow awesome guys. All the extra input is greatly appreciated. It helps me learn the formulas. Again very thankful for everyone input on this. You guys are awesome!