IF(and logic formula error Please help on same

[JawaharPrem]

Hi All,

Iam working on Multilple drop down dashboard, iam getting error.
Example - in coloum "B3" 1st drop down given if i select one the zones i will get that particulare zone its is working fine.

example2- in coloum "B5", 2nd drop donw given if i select ex-"tele" and in "B3" any zone that particular count should be update.

i have tried iam getting error.
Formula pasted, sample file updated. Please help on same.

=IF($B$3="overall",IF(AND($B$5="overall"),IF(AND(B3="overall",B5<>"overall"),COUNTIFS(Sheet2!$D:$D,'Zone & Source Wise'!E$5,Sheet2!$B:$B,'Zone & Source Wise'!$D6),IF(AND(B3<>"overall",B5="overall"),COUNTIFS(Sheet2!$D:$D,'Zone & Source Wise'!E$5,Sheet2!$B:$B,'Zone & Source Wise'!$D6))))),IF(AND(B3="overall",B5<>"overall"),COUNTIFS(Sheet2!$D:$D,'Zone & Source Wise'!E$5,Sheet2!$B:$B,'Zone & Source Wise'!$D6,Sheet2!$A:$A,'Zone & Source Wise'!$B$3),IF(AND(B3<>"overall",B5="overall"),COUNTIFS(Sheet2!$D:$D,'Zone & Source Wise'!E$5,Sheet2!$B:$B,'Zone & Source Wise'!$D6,Sheet2!$C:$C,'Zone & Source Wise'!$B$5))))

Thanks
Jawahar Prem

 

[SirJB7]

Hi, JawaharPrem!
In what cell are you using the posted formula? I can't find it in the uploaded workbook.
Regards!

 

[JawaharPrem]

Hi Sir,
the formula to updated in "E6" i have worked for single drop down.


Thanks
Jawahar Prem

 

[JawaharPrem]

Hi Sir,

I worked on this forumula. Please check this is working..

=IF($B$5="over all",IF($B$3="over all",COUNTIFS(Sheet2!$B:$B,'Zone & Source Wise'!$D6,Sheet2!$D:$D,'Zone & Source Wise'!E$5),COUNTIFS(Sheet2!$B:$B,'Zone & Source Wise'!$D6,Sheet2!$D:$D,'Zone & Source Wise'!E$5,Sheet2!$A:$A,'Zone & Source Wise'!$B$3)),IF($B$3="over all",COUNTIFS(Sheet2!$B:$B,'Zone & Source Wise'!$D6,Sheet2!$D:$D,'Zone & Source Wise'!E$5,Sheet2!$C:$C,'Zone & Source Wise'!$B$5),COUNTIFS(Sheet2!$B:$B,'Zone & Source Wise'!$D6,Sheet2!$D:$D,'Zone & Source Wise'!E$5,Sheet2!$A:$A,'Zone & Source Wise'!$B$3,Sheet2!$C:$C,'Zone & Source Wise'!$B$5)))

thanks
Jawahar Prem

 

[SirJB7]

Hi, JawaharPrem!
Doesn't it work for you? I tested for TRICHY, Over All, Retail P2P, and I got 263 which matches the filtered records.
Regards!

 

[JawaharPrem]

Hi sir,

Thanks for your time, It works. i enclosed sample file.

In Zone = Trichy
Cat = Overall i get count 312 as in raw data.

Thanks
Jawahar Prem

 

[JawaharPrem]

Hi Sir,
Iam working on these types reports with drop down and radio button's, is there any easy formula more than If. Please help on same.