How to link a countifs formula to a cell that changes according to another formula

Occasional Visitor

Hi there, 


I'm trying to create a dashboard template for a work project, but am having trouble linking a formula properly. I want this dashboard to take data from another sheet, which involves category of a company & the status of their application to an event.

I've used a COUNTIFS function to check how many companies are both: of a certain application status, and of a certain category (ie. In contact, Startup). However, the category I am trying to check the data against will change depending on each project my colleagues work on. Therefore, I would like to know how to link the COUNTIFS function to a cell that itself is a simple formula that changes value according with a cell in a different sheet. 


The formula I currently have:

Screenshot 2022-09-09 at 13.57.25.png


The cell that will change depending on a value in a different sheet:


Screenshot 2022-09-09 at 13.56.54.png


Thanks very much, and let me know if anything is unclear!

1 Reply
I believe your problem is NOT that your are reference that other cell but rather that your 2 ranges don't have the same number of elements. One starts on row 1 and the other starts on row 3.