I have an Excel sheet where I have multiple tasks and multiple products. Each task for each product is rated red. amber or green depending on progress so far. Each task also has an owner.
I want to create another table which shows how many red, amber and green ratings are assigned to each owner. I can do this very easily with a countif function, but the additional problem is that new tasks and owners are being added to the bottom of my list by others in my dept. So the only way I can keep my table up to date is to constantly reorder the table by owner, and adjust the ranges in the countif.
Here's a link to an example I made:
I've found a few Index functions that can return multiple values and count up the values but I'm not smart enough to make them work across multiple columns.
Thanks,
答案1
For your current layout, you can use this in cell J4:
=SUMPRODUCT((OFFSET($D$3,0,0,COUNTA($C:$C)-1,COUNTA($2:$2)-2)=J$3)*(OFFSET($C$3,0,0,COUNTA($C:$C)-1)=$I4))
COUNTA($C:$C)-1 returns the height of the data table (the -1 is to disregard the header row). This relies on column C which contains the owners and there shouldn't be data after the last owner or contain blanks between rows in the data itself.
COUNTA($2:$2)-2 similarly returns the breadth of the data table (and this time, I have -2 because there are 2 columns before the dataset starts). Again, there shouldn't be missing headers in the table.
(OFFSET($D$3,0,0,COUNTA($C:$C)-1,COUNTA($2:$2)-2) returns the range starting at D3 and with height and breadth calculated above.
OFFSET($C$3,0,0,COUNTA($C:$C)-1) returns the range containing the owners.
(OFFSET($D$3,0,0,COUNTA($C:$C)-1,COUNTA($2:$2)-2)=J$3) compares the data with the cell J3. In my table, I put r in J3 so that I could drag the formula across the table only twice without having to change anything in the formula, but you can substitute the J$3 with "r", you'll just have to adjust it for each column once. The expression will return TRUE for matches and FALSE otherwise.
(OFFSET($C$3,0,0,COUNTA($C:$C)-1)=$I4) compares the owners with the owner in cell I4. Returns TRUE for matches and FALSE otherwise.
When the above two results are multiplied together, you end up with a series of 1 and 0 which SUMPRODUCT adds up to give a count.