CODEWITHSUNDEEP
excel
theoldmn
theoldmn

Reputation: 21

Excel: Look up specific value over an array and then count the number of items in specific column

This is my first post and am only creating this post after not being able to find my specific issue. I am trying to count specific items in a certain column if that column has a specific heading.

Example: My work has an Excel file that different team members mark once they've completed a specific task. I then have a table on another spreadsheet that updates how many tasks a specific team member has completed. The team members have to make these marks every quarter.

enter image description here

The above image best illustrates what I mean. I would like the COUNTIFS function to automatically update based on cell A15; IE when i change the date, the counter will update for the corresponding column. Right now, I manually adjust the criteria range for every period. I know there must be a way to tie to cell A15 so when I change the cell value, everything else updates automatically. I just can't figure out how to do it. Hopefully someone can help!

Thanks.

Upvotes: 2

Views: 730

Answers (2)

Tom Malkin
Tom Malkin

Reputation: 2284

You can do this with multiple criteria using an array formula, e.g.

=SUM(IF(IF(B1:D1=A9,IF(A2:A7=A10,B2:D7))="X",1))

Where

=SUM(IF(IF([Date Range]=[Date Criteria],IF([Team Member Range]=[Team Member Criteria],[All the Xs]))="X",1))

Remember to press Control+Shift+Enter when entering an array formula

Upvotes: 1

Kuhron
Kuhron

Reputation: 66

You can look up the index of the column using MATCH and use that to offset the range. For example:

image

The formula in my example is =COUNTIF(OFFSET(A2:A7, 0, MATCH(A9, A1:C1, 0)-1), "=x"). For you I would use =COUNTIF(OFFSET(A3:A12, 0, MATCH(A15, A2:E2, 0)-1), "=X").

Upvotes: 1

Related Questions