Averageif Formula Help

[sayankolay]

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.



[TABLE="width: 730"]
<tbody>[TR]
[TD]Parameters
[/TD]
[TD]Overall
[/TD]
[TD]APAC
[/TD]
[TD]EMEA
[/TD]
[TD]America
[/TD]
[/TR]
[TR]
[TD]Script Accuracy
[/TD]
[TD]91.67%
[/TD]
[TD]Need Help
[/TD]
[TD]Need Help
[/TD]
[TD]Need Help
[/TD]
[/TR]
</tbody>[/TABLE]

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

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

 

[etaf]

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

 

[BQardi]

Use COUNTIFS (assuming the header APAC is in C1):

=(COUNTIFS(D3:D50,"Yes",C3:C50,C2))/((COUNTIFS(D3:D50,"Yes",C3:C50,C2)+(COUNTIFS(D3:D50,"No",C3:C50,C2))))

Copy over...

 

[BQardi]

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

 

[sayankolay]

Thanks a ton It worked both ways ..

 

[etaf]

you are welcome