Averageif Formula Help

sayankolay

New Member
Joined
Feb 28, 2016
Messages
5
Hi Experts....

I am trying to get Region wise "Script Accuracy" for the following transactions. I am using the following formula for extracting overall accuracy =(COUNTIF(D3:D50,"Yes"))/((COUNTIF(D3:D50,"Yes")+(COUNTIF(D3:D50,"No")))).

But I need Region wise accuracy as well. I know using averageif formula it is possible to get results for specific groups in the text column but need help in creating such a formula.

Any help would be help would be highly appreciated. I have pastes the excel sheet for better understanding.



Parameters
Overall
APAC
EMEA
America
Script Accuracy
91.67%
Need Help
Need Help
Need Help

<tbody>
</tbody>

Overall Formula "=(COUNTIF(D3:D50,"Yes"))/((COUNTIF(D3:D50,"Yes")+(COUNTIF(D3:D50,"No"))))"

SL No
Ticket No.
Region
Script Accuracy
1
CALL0003447753
APAC
No
2
CALL0003446922
APAC
Yes
3
CALL0003445008
APAC
Yes
4
CALL0003445485
APAC
Yes
5
CALL0003446325
APAC
Yes
6
CALL0003447267
APAC
Yes
7
CALL0003451704
APAC
Yes
8
CALL0003452183
APAC
Yes
9
CALL0003446836
APAC
Yes
10
CALL0003451082
APAC
Yes
11
CALL0003461223
APAC
Yes
12
CALL0003452397
APAC
Yes
13
CALL0003446646
APAC
Yes
14
CALL0003464640
APAC
Yes
15
CALL0003455839
APAC
Yes
16
CALL0003458201
APAC
Yes
17
CALL0003411814
APAC
Yes
18
CALL0003420181
EMEA
No
19
CALL0003414241
EMEA
Yes
20
CALL0003418776
EMEA
Yes
21
CALL0003411691
EMEA
Yes
22
CALL0003420572
EMEA
Yes
23
CALL0003418692
EMEA
Yes
24
CALL0003411515
EMEA
Yes
25
CALL0003419926
EMEA
Yes
26
CALL0003418607
EMEA
Yes
27
CALL0003422077
EMEA
Yes
28
CALL0003422021
EMEA
Yes
29
CALL0003427557
EMEA
Yes
30
CALL0003427258
EMEA
Yes
31
CALL0003425163
EMEA
Yes
32
CALL0003419421
EMEA
Yes
33
CALL0003422726
EMEA
Yes
34
CALL0003468110
EMEA
Yes
35
CALL0003446281
EMEA
Yes
36
CALL0003452960
EMEA
Yes
37
CALL0003452446
EMEA
Yes
38
INC0005133125
America
Yes
39
INC0005129575
America
Yes
40
INC0005129019
America
Yes
41
INC0005127498
America
Yes
42
INC0005126822
America
Yes
43
INC0005134335
America
Yes
44
INC0005139819
America
Yes
45
INC0005136361
America
Yes
46
INC0005137154
America
Yes
47
INC0005138215
America
No
48
INC0005135585
America
No

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,391
Office Version
  1. 365
Platform
  1. MacOS
using the same type of calc as you are using

countifs(C3:C50, "APAC", D3:D30, "YES")
will give the count of the region APAC and YES
so then divide by the total for the region
APAC
countif(C3:C50, "
APAC")

=countifs(C3:C50, "APAC", D3:D30, "YES") / countif(C3:C50, "APAC")

will give the % for the region "APAC"

you could use the cell that contains the region
assuming row1 stating in column C

=countifs($C$3:$C$50, C$1, $D$3:$D$30, "YES") / countif($C$3:$C$50, C$1)
and copy to D and E


 
Last edited:

BQardi

Active Member
Joined
Aug 30, 2016
Messages
483
Use COUNTIFS (assuming the header APAC is in C1):
Code:
=(COUNTIFS(D3:D50,"Yes",C3:C50,C2))/((COUNTIFS(D3:D50,"Yes",C3:C50,C2)+(COUNTIFS(D3:D50,"No",C3:C50,C2))))
Copy over...
 

BQardi

Active Member
Joined
Aug 30, 2016
Messages
483
using the same type of calc as you are using

countifs(C3:C50, "APAC", D3:D30, "YES")
will give the count of the region APAC and YES
so then divide by the total for the region
APAC
countif(C3:C50, "
APAC")

=countifs(C3:C50, "APAC", D3:D30, "YES") / countif(C3:C50, "APAC")

will give the % for the region "APAC"

you could use the cell that contains the region
assuming row1 stating in column C

=countifs($C$3:$C$50, C$1, $D$3:$D$30, "YES") / countif($C$3:$C$50, C$1)
and copy to D and E



Better and shorter;)

EDIT: But you should probably change $D$3:$D$30 to $D$3:$D$50
 
Last edited:

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,391
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,569
Members
413,996
Latest member
mabelO

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