Countif ?

[jritchey]

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

 

[T. Valko]

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]

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]

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...

Book1
	F
2	9786136324
3	24
4	24
5	124
6	2424
7	243
8	324
9	6389248977
Sheet1

=COUNTIF(F:F,24)

Result = 2

 

[jritchey]

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]

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.