reference cell as sheet in criteria in countif formula

Question

I have a countif formula that gathers information from another sheet.

=SUM(COUNTIFS(test!A:A,{"Story","Task"},test!D:D,{"In Progress"}))

The sheet is named test.

Is there any way I can write test in another cell and reference it in the formula?

Eg: Cell A3 = test

=SUM(COUNTIFS(A3!A:A,{"Story","Task"},A3!D:D,{"In Progress"}))

Welcome. Please check How to Ask and About asking. As per site guidelines when an answer addresses your question, you should accept it and maybe upvote it so others can benefit as well. — marikamitsos, Commented Sep 20, 2019 at 14:17

marikamitsos · Accepted Answer · 2019-09-20 14:31:23Z

0

The function to use is INDIRECT

So A3=test will be =indirect("A"&"3")
That is because INDIRECT returns the cell reference using the two parameters concatenated by &.

In your formula A3 becomes the first parameter and A:A the second, resulting to:

=SUM(COUNTIFS((indirect(A3&"!"&"A:A"),{"Story","Task"}, 
              (indirect(A3&"!"&"D:D"),{"In Progress"}))

You can read more about INDIRECT.

answered Sep 20, 2019 at 14:08

marikamitsos

13.1k4 gold badges34 silver badges59 bronze badges

Add a comment |

1 Answer 1