hi kishoremcp. selecting "Overall" means it's going to go through the list to find the word "Overall" like it did for US&C,EAMER or GAR. it will not add up all of them. i know you added a word "Overall" in the Raw Data. but it's going to fail your COUNTIF criteria because it has only 1 row & the rest of the cells are empty. i did an IF formula in front to say if B2 in "Input" is "Overall", it will exclude 1 criteria in your COUNTIFS. it will not check for any names in Region. otherwise, do your formula you provided. it's pretty lengthy, but it's basically just a little repeat of your formula. hope that works for you.
=IF(Input!$B$2="Overall",IFERROR(SUMPRODUCT(COUNTIFS('Raw Data'!$P$2:$P$20000,$A2,'Raw Data'!$X$2:$X$20000,">=6",'Raw Data'!$E$2:$E$20000,Rough!M$1:M$2,'Raw Data'!$G$2:$G$20000,Input!$C$2))/SUMPRODUCT(COUNTIFS('Raw Data'!$P$2:$P$20000,$A2,'Raw Data'!$X$2:$X$20000,">=0",'Raw Data'!$E$2:$E$20000,Rough!M$1:M$2,'Raw Data'!$G$2:$G$20000,Input!$C$2))," "),IFERROR(SUMPRODUCT(COUNTIFS('Raw Data'!$P$2:$P$20000,$A2,'Raw Data'!$X$2:$X$20000,">=6",'Raw Data'!$A$2:$A$20000,Input!$B$2,'Raw Data'!$E$2:$E$20000,Rough!M$1:M$2,'Raw Data'!$G$2:$G$20000,Input!$C$2))/SUMPRODUCT(COUNTIFS('Raw Data'!$P$2:$P$20000,$A2,'Raw Data'!$X$2:$X$20000,">=0",'Raw Data'!$A$2:$A$20000,Input!$B$2,'Raw Data'!$E$2:$E$20000,Rough!M$1:M$2,'Raw Data'!$G$2:$G$20000,Input!$C$2))," "))
Bookmarks