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.

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>

 

[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