Adding IF and COUNTIF together?

[Jheye]

Here is my worksheet example. I need a formula to count how many times the 'Service Level %' was greater than 79.999% for each day of the week. So for Monday, there is two data points, but, 1 would be my answer in this example.
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Weekday[/TD]
[TD="align: center"]Service Level %[/TD]
[TD="align: center"]Calls Offered[/TD]
[TD="align: center"]Work days[/TD]
[/TR]
[TR]
[TD="align: center"]Mon[/TD]
[TD="align: center"]81%[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Tues[/TD]
[TD="align: center"]73%[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Wed[/TD]
[TD="align: center"]60%[/TD]
[TD="align: center"]159[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Thur[/TD]
[TD="align: center"]90%[/TD]
[TD="align: center"]232[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Fri[/TD]
[TD="align: center"]82%[/TD]
[TD="align: center"]278[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Mon[/TD]
[TD="align: center"]64%[/TD]
[TD="align: center"]132[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Tue[/TD]
[TD="align: center"]79%[/TD]
[TD="align: center"]145[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Wed[/TD]
[TD="align: center"]89%[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Welcome to the Board!

Take a look at COUNTIFS, which allows you to use more than one condition.
See: MS Excel: How to use the COUNTIFS Function (WS)

 

[Jheye]

Thanks man, appreciate the quick reply. This is just what I needed!

I have a follow up thought/question on this that maybe you can point me in the direction of.

Now, continuing from the above formula, using a COUNTIFS function, I look at criteria 1) days that are Monday and 2) take the 'Service Level %' that is greater than 79.999% and then 3) take those specific values of 'Service Level%' and average them. How would I add on that 3rd step to perform a calc at the end?

 

[par60056]

There is another function SUMIFS. Use sumifs to add the % together and divide by the countifs

 

[Jonmo1]

You can use AVERAGEIFS for that.

 

[Jonmo1]

Although it would be remiss to not mention that averageing percentages may not be accurate.
Depending on how each percentage is obtained.

So you're dealing with Service Level percentages.
That is some sort of sum divided by count.
Something like (Sum of all calls) divided by (count of calls handled in less than x minutes)

If the COUNT for monday1 is different than the count for monday2, then average percentage of monday1 and monday2 will not be accurate.


Think about a baseball team, they have batting averages.
The TEAM's average is NOT calculated by the average of each player's average.
Consider that the pitcher has far fewer at bats than other players.
So the pitcher's average would have greater influence on that average than the other players.
So you have to do the average of EVERYONE, ALL At Bats by the team, regardless of the player.

 

[Jheye]

Jonmo1 - I agree with you, the percentages would not be the best metric to average out in that way.

Thank you for the quick replies everyone! These functions are great. That 'Techonthenet" site is loaded with goodies.