Need help in Index Match function

Copper Contributor

Hi, I am trying to Count no.s equals to ZERO from column DB with this formula, but getting error. Can anyone help me.

 

There are 2 sheets in same workbook. I am trying to countif some data.

Formula used is : 

=COUNTIFS(Dealer!$D:$D,State!$B5,Dealer!$K:$K,"Active",Dealer!DB:DB,"<=0")

This formula works well. But...

The case is, I don't want to use DB:DB in this formula, I want excel to refer this column automatically. That's why I tried using Index Match (referring current Month as "July" & "B")

 

Refer cell DB1 = 7B

Refer cell V1 = 7B (in 2nd sheet)

 

=COUNTIFS(Dealer!$D:$D,$B5,Dealer!$K:$K,"Active",INDEX(Dealer!$CP$4:$DE$1137,,MATCH($V$1,Dealer!$CP$1:$DE$1,0)),"<=0")

 

Don't know what is wrong in this formula. Please help.

sheet 2.jpgfile 1.jpg

4 Replies
If a picture paints a thousand words, a sample Excel file, with dummy data and manually entered results, paints a million explanations!

@Sarav45 , in second INDEX the range shall be same size, like

INDEX(Dealer!$CP:$DE,0,MATCH($V$1,Dealer!$CP$1:$DE$1,0))

 

thank you so much, my bad....

@Sarav45 , you are welcome