Insert Function with values from other Worksheet

Question

I'm not too good with Insert Function in Excel...

I basically built a table on a worksheet called Alunos, and want to do a countif from this worksheet to another.

I figured out how to copy content from a cell to another worksheet cell by typing: =Alunos!K9 (this copies the content from K9 of worksheet Alunos to a cell in my new Worksheet)

But now I want to do something a but more complex, I want to look up all the values in a column and count them, so I tried:

=Alunos!COUNTIF(AnoInscr:F200,"2008")

This is returning me an error on Alunos and I can't seem to figure it out...

On another less important question, is there a way for the function to search all the values in a column without me having to give a max value? In my example above I used F200 because I know my values won't reach cell 200, but this seems rather bad weak programming (if we can call it that).

Answer 1

=COUNTIF(Alunos!F:F,"2008")

F:F means the whole column.

your alunos was in the wrong place it belongs on the range you are defining, not the function.

Answer 2

=COUNTIF(Alunos!myTableName[AnoInscr], "2008")

Assuming that

• Alunos is the name of sheet.
• myTableName is the name of the table.
• AnoInscr is the name of the column inside table myTableName.