Divide two sumifs formula?

N

njsutorius

New Member
Joined
Apr 6, 2014
Messages
43
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!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
=sumproduct(--($b$2:$b$6=f2),$d$2:$d$6)/(sumproduct(--($a$2:$a$6>=h$2),--($a$2:$a$6<=h$3),--($b$2:$b$6=f2),$c$2:$c$6)*24)
 
Upvote 0
You see where im going wrong on here? It doesn't seem to be picking up the second date marker.

=sumproduct(--($E$10:$E$84=E10),'Production data'!$e:$e)/(sumproduct(--('Production data'!$A:$A>='Production Summary'!F5),--('Production data!$A:$A<='Production Summary'!F6:G6),--($E$10:$E$84=E10),'Production data'!$D:$D)*24)
 
Upvote 0
arrays have to be the same range in this case, but it's hard to see withou the actual data.
=sumproduct(--($E:$E=E10),'Production data'!$e:$e)/(sumproduct(--('Production data'!$A:$A>='Production Summary'!F5),--('Production data!$A:$A<='Production Summary'!F6),--($E:$E=E10),'Production data'!$D:$D)*24)
<colgroup><col width="1318" style="width: 989pt; mso-width-source: userset; mso-width-alt: 48201;"> <tbody> </tbody>
 
Upvote 0
vogel997 said:
arrays have to be the same range in this case, but it's hard to see withou the actual data.
=sumproduct(--($E:$E=E10),'Production data'!$e:$e)/(sumproduct(--('Production data'!$A:$A>='Production Summary'!F5),--('Production data!$A:$A<='Production Summary'!F6),--($E:$E=E10),'Production data'!$D:$D)*24)

<tbody>
</tbody>
Click to expand...

When you say the arrays have to be the same range, are you referring to the range being the 1) that it matches the agent id first, and then 2) that its within a specific date range?


Here is s sample of the 'production summary' sheet . #value is where im putting the equation in.

b
c
d
e
f
g
4
 Fixed
5
Date from
3/31/14
6
Date to3/31/14
7
8
Weekly
9
DeptAgent NameAgent IDScoreCPH
10
D1Employee 1116
 #VALUE!

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>


here is the a sample of the data on the 'production data' sheet.

a
b
c
d
e
f
1
Date
Agent idNameHours logged into phoneCalls takenHours in decimal
2
3/31/2014102
name
32:44:0033832.73
3
3/31/2014103name
28:29:0037028.48
4
3/31/2014107name7:45:00717.75
5
3/31/2014108name29:23:0028429.38
6
3/31/2014112name
32:46:0029632.77
7
3/31/2014113name29:40:0028029.67
8
3/31/2014114name
25:08:0022625.13
9
3/31/2014116name
11:10:0010411.17
10
3/31/2014117name
36:45:0011736.75
11
3/31/2014124name
30:00:0026330.00

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Does this help?
 
Upvote 0
Date from3/31/2014
Date to3/31/2014
Weekly
DeptAgent NameAgent IDScoreCPHmaybe this helps?
D1Employee 1116 9.310653536=SUMPRODUCT(--('Production data'!B2:B11=E7),'Production data'!E2:E11)/(SUMPRODUCT(--('Production data'!B2:B11=E7),--('Production data'!A2:A11>=F2),--('Production data'!A2:A11<=F3),'Production data'!F2:F11))
<colgroup><col width="64" style="width: 48pt;" span="3"> <col width="97" style="width: 73pt; mso-width-source: userset; mso-width-alt: 3547;"> <col width="144" style="width: 108pt; mso-width-source: userset; mso-width-alt: 5266;"> <col width="757" style="width: 568pt; mso-width-source: userset; mso-width-alt: 27684;"> <tbody> </tbody>
 
Upvote 0
you can extend the arrays but all need to be the same range
B2:B11 to B2:B2000 and E2:E11 to E2:E2000 etc..
<colgroup><col width="757" style="width: 568pt; mso-width-source: userset; mso-width-alt: 27684;"> <tbody> </tbody>
 
Upvote 0
vogel997 said:
Date from3/31/2014
Date to3/31/2014
Weekly
DeptAgent NameAgent IDScoreCPHmaybe this helps?
D1Employee 11169.310653536=SUMPRODUCT(--('Production data'!B2:B11=E7),'Production data'!E2:E11)/(SUMPRODUCT(--('Production data'!B2:B11=E7),--('Production data'!A2:A11>=F2),--('Production data'!A2:A11<=F3),'Production data'!F2:F11))

<tbody>
</tbody>
Click to expand...

I think we are almost there.

I change E7 to E10 so it is match 116 and i changed the fields that hold the dates to properly match my sheet. Now i get a "#div0!" error.

heres my code with the adjusted boxes.

=SUMPRODUCT(--('Production data'!B2:B11=E10),'Production data'!E2:E11)/(SUMPRODUCT(--('Production data'!B2:B11=E7),--('Production data'!A2:A11>=F5),--('Production data'!A2:A11<=F6),'Production data'!F2:F11))
 
Upvote 0
need to change E7 to E10 in second part
=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>
 
Upvote 0
You must log in or register to reply here.

Similar threads

tezza
  • Question
Excel calculation wrong ... sometimes, query
Replies
3
Views
89
KRice
K
R
  • Question
Help! VBA to loop through a list containing data references to update forecast sheets automatically
Replies
3
Views
122
Domenic
D
F
  • Question
Conditional format based on time
Replies
2
Views
83
Fegal
F
A
  • Solved
Production time calculation within the working day accounting for breaks
2
Replies
16
Views
688
felixstraube
F
P
  • Question
Analytics Project - Sumifs
Replies
1
Views
229
bobaol
B

Forum statistics

Threads
1,215,028
Messages
6,122,749
Members
449,094
Latest member
dsharae57

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top