COUNTIF inside INDIRECT in Google Sheets

0
votes

I am trying to count the number of letter "A" in range E6:AI6 from tab name "FINAL" in different tab name "FINAL DATA"

I basically want to look up 'FINAL'!E6:AI6, This is what I have tried:

=COUNTIF(INDIRECT("'FINAL'!E6:AI6" & E6 & ":A" & AI6),1)

but it is returning 0 always but in a particular range, there are 5 cells whose value is "A"

=COUNTIF(INDIRECT("'FINAL'!E6:AI6" & E6 & ":A" & AI6),1)

It should return 5 instead of 0 cell where I am applying above function

2
google-sheetsgoogle-sheets-formula

2 Answers

1
votes

If I'm' understanding your question correctly, you'd like to count the occurrence of a certain string in a certain range different sheet within the same spreadsheet in Google Sheets.

I think the following should do the job:

=COUNTIF(FINAL!E6:AI6,"A")

EDIT:

Including INDIRECT would work the following way.

=COUNTIF(INDIRECT("'FINAL'!E6:AI6"),"A")

I understand that this doesn't solve the issue you've described in the comments, but I think it addresses the question asked in the initial post.

-1
votes

not sure which one you need but it should look something like this:

  • =INDIRECT(E6 & COUNTIF(FINAL!E6:AI6,"A") & ":A" & AI6)
  • =INDIRECT(FINAL!E6 & COUNTIF(FINAL!E6:AI6,"A") & ":A" & FINAL!AI6)