How to count values within a dyanmic column in a range in another sheet?

Paul Lambert

Hi All,

I have a helper sheet (Sheet A) that I use to count cells in another sheet (Sheet B) based on various criteria. In one scenario, I'd like to count the number of cells in a column in Sheet B by matching various criteria. The challenge is that I'd like the formula to dynamically choose the column in Sheet B by matching a value in Sheet A to value in the first row of Sheet B. I can create separate range references for each column, but given the number of columns, I'd prefer to select the column dynamically. Thanks in advance.

Regards,

Paul

Paul Newcome

Are you able to provide screenshots with sample data for reference?

Paul Lambert

Hi Paul,

Here are some sample sheets that I setup to illustrate what I'm trying to do:

I'd like to be able to create formulae in the yellow section of Sheet A that use the Metric column and first row to count the number of occurences that match the corresponding status and app. For now, I've implemented this by creating separate references for each of columns App1 to App4. Thanks in advance.

Paul Newcome

Unfortunately you are going to have to stick with separate references.