CODEWITHSUNDEEP
excel
Ba Tung Dinh
Ba Tung Dinh

Reputation: 21

How to refer a cell data into countifs formula?

My excel image:

enter image description here

At E6 cell, I used this formula:

=countifs(Example1!$L$19:$L$1000;$B$6;Exampale1!$M$19:$M$1000;$C$6)

And at F6 cell, i used this formula with C8 cell contains the sheetname that I want to refer:

=countifs('INDIRECT(C8)'!$L$19:$L$1000;$B$6;'INDIRECT(C8)'!$M$19:$M$1000;$C$6)

why are there 2 different returned results?

As expected, I want F6 will return as E6.

I tried to search but nothing can resolve.

Upvotes: 1

Views: 74

Answers (1)

Vipul Karkar
Vipul Karkar

Reputation: 327

That's not how indirect works.

Replace the formula in F6 as follow.

=COUNTIFS(INDIRECT("'"&C8&"'!"&"$L$19:$L$1000"),$B$6,INDIRECT("'"&C8&"'!"&"$M$19:$M$1000"),$C$6)

Upvotes: 2

Related Questions