Countif ?

jritchey

New Member
Joined
Jul 10, 2012
Messages
3
Ok...

So I have a report that shows all inbound and outbound calls that are made each day. For some reason our IT guy set up everyone's extensions as two digits, 24 for example. I have been trying to use a countif function to count everytime 24 appears in column F, but it not only counts the instances that I want but also if 5555555524 is in column F too.

Can someone please provide some assistance to how I can only count the instances of 24 in column F and not when other phone numbers appear that have 24 in them?

Thanks
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Ok...

So I have a report that shows all inbound and outbound calls that are made each day. For some reason our IT guy set up everyone's extensions as two digits, 24 for example. I have been trying to use a countif function to count everytime 24 appears in column F, but it not only counts the instances that I want but also if 5555555524 is in column F too.

Can someone please provide some assistance to how I can only count the instances of 24 in column F and not when other phone numbers appear that have 24 in them?

Thanks
Like this...

=COUNTIF(F:F,24)
 

jritchey

New Member
Joined
Jul 10, 2012
Messages
3
That's what I tried to use, but it doesn't work as it will count when 24 appears but also when 9786136324 or 6389248977 appears as the number 24 is in those phone numbers. I need a way to eliminate those instances and only count when my rep who has the number 24 assigned as their ext comes up.
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
That's what I tried to use, but it doesn't work as it will count when 24 appears but also when 9786136324 or 6389248977 appears as the number 24 is in those phone numbers. I need a way to eliminate those instances and only count when my rep who has the number 24 assigned as their ext comes up.
No, it only counts cells that contain 24...

<b>Sheet1</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:103px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >F</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; border-style:solid; border-width:1px; border-color:#000000; ">9786136324</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">24</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">24</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">124</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">2424</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">243</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">324</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">6389248977</td></tr></table> <br /><br />
=COUNTIF(F:F,24)

Result = 2
 

jritchey

New Member
Joined
Jul 10, 2012
Messages
3
Ok... I walked away and started a new sheet and that is now working correctly... I'm sure I was screwing something up, but it's good now. Thanks for the help with that.

Now I have a second part to it that I need a solution for. In Column I it shows the number my rep called, but when I do the count if I don't want it to count column F if Column I has *64 or *65 as that is when the log in and out of their phone. Is there a way to add this to the countif or another way to do this?
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Ok... I walked away and started a new sheet and that is now working correctly... I'm sure I was screwing something up, but it's good now. Thanks for the help with that.

Now I have a second part to it that I need a solution for. In Column I it shows the number my rep called, but when I do the count if I don't want it to count column F if Column I has *64 or *65 as that is when the log in and out of their phone. Is there a way to add this to the countif or another way to do this?
Is the * a wildcard?

If so, try this...

=SUMPRODUCT(--(F2:F10=24),--(ISNA(MATCH(RIGHT(I2:I10,2),{"64","65"},0))))

Note that with SUMPRODUCT you *can't* use entire columns as range references unless you're using Excel 2007 or later. And, even if you are using Excel 2007 or later, you should avoid using entire columns as range references with SUMPRODUCT.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,647
Messages
5,597,363
Members
414,139
Latest member
okela0

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
Top