Combined count if and index match

To visualize my problem, please download attach file first. How to count completed status across rows and column all the related Company code and task code on other tab back to summary tab if the arrange of column is vice versa. Hope you could help me. Below list of requirements.


1. I have 2 tabs, Summary and Employee Name. Employee will input on Employee name tab completed task with date. And will be summarize back to Summary tab
2. As you notice Company code on this Summary tab is by arrange by rows on the other tab(Employee name tab) it is arrange by column,
3. As well the Task code on this tab arrange by column and vice versa on other tab
4. I want to count respective text per Company code & Task code using index count if all completed items across sheet. All counts must be convert to percentage.

I'm not 100% sure that I understand what you're looking for, but I think if you use:

Formula:

Please Login or Register  to view this content.

...in Cell C3 on your 'Summary' sheet, it'll return the total of "4" that you're looking for. If you want a percentage, include '/12' at the end of the formula and format the cell as a percentage. Modify the range for C4, D3, and D4 and you should be all set, right? It'll update the numbers in 'Summary' as the cells are filled in 'Employee Name'. As best I can tell, that's what you're looking for. If I'm wrong, please clarify your request.

Hi @Cantosh,my spreadsheet attach is just an example my problem, the original contains more task code and multiple company codes. If the range has been expanded, it will be ease for the user not to expand or modify again the formula just to capture the exact range. Is there any formula that can capture task and company code as an lookup value?thanks

Up for this thread. thanks

I'm not sure I follow you. The ranges of my COUNTA formulas should expand as you insert new rows in the 'Employee Name' ranges. Alternatively, you could name each company code range for additional clarity. A countif or lookup formula will still have to specify a range. Can you clarify what's lacking?

Formula:

Please Login or Register  to view this content.