Adding numerator and denominator in same cell

dsacomano

New Member
Joined
Jul 17, 2019
Messages
5
Not sure if this is possible, but I am looking to add both the numerator and denominators and total them in a final cell. It is to track individual phone calls answered vs total phone calls received from a call center.

Example.

B2- 74/326 (74 answered, 326 total calls)
C2- 55/245
D2- 81/195

I am hoping to add these so that E2 will show 210/766.

Any help would be greatly appreciated. Thanks.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,451
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">74/326</td><td style=";">55/245</td><td style=";">81/195</td><td style=";">210/766</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Request</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">{=SUM(<font color="Blue">--LEFT(<font color="Red">B2:D2,FIND(<font color="Green">"/",B2:D2</font>)-1</font>)</font>)&"/"&SUM(<font color="Blue">--MID(<font color="Red">B2:D2,FIND(<font color="Green">"/",B2:D2</font>)+1,100</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

dsacomano

New Member
Joined
Jul 17, 2019
Messages
5
When I use Ctrl+Shift+Enter to enter this formula, it doesn't give me the brackets. I have tried copying and pasting and typing the formula manually.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,451
Office Version
365
Platform
Windows
Enter the formula without the {} & whilst in Edit mode hit Ctrl Shift Enter
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,511
Office Version
2010
Platform
Windows
Hi & welcome to MrExcel.
How about

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">74/326</td><td style=";">55/245</td><td style=";">81/195</td><td style=";">210/766</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Request</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">{=SUM(<font color="Blue">--LEFT(<font color="Red">B2:D2,FIND(<font color="Green">"/",B2:D2</font>)-1</font>)</font>)&"/"&SUM(<font color="Blue">--MID(<font color="Red">B2:D2,FIND(<font color="Green">"/",B2:D2</font>)+1,100</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
For those uncomfortable with array-entered formulas, you can turn Fluff's array-entered formula into a normally-entered formula by using SUMPRODUCT instead of SUM function calls...

=SUMPRODUCT(--LEFT(B2:D2,FIND("/",B2:D2)-1))&"/"&SUMPRODUCT(--MID(B2:D2,FIND("/",B2:D2)+1,100))
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,451
Office Version
365
Platform
Windows
Excellent point Rick.
Thanks for that.
 

Forum statistics

Threads
1,082,641
Messages
5,366,717
Members
400,915
Latest member
NFlem

Some videos you may like

This Week's Hot Topics

Top