Excel Formula to create sum of number of cells filled per row per selective columns
Hey guys
I’m wondering if you could help or perhaps suggest a resolution to my current issue
I have a live ‘cube’ of data against which I have some target statistics set up.
Some of these statistics are derived from a calculation, per row (these are individual entries in the database), that looks at specific cells and if they are ‘completed’ or filled with data.
What I’m having problems with is creating a formula in Column BE (VBA?) that will look at each row and look at specific cells and if these cells are filled, then add 1 to the calculation up to a maximum of 23.
I have attached an extract from the actual sheet. The yellow highlighted columns are the columns that I wish to look at for conformity ie. the cells on each row within those columns are completed. All other columns I need disregarded as part of the calculation.
Ive put in a sample calculation in Column BE for the last two row entries just to show what the result Im trying to accomplish, with the cells on the last two row highlighted in green to show the missing data that would culminate in the resulting sum being displayed.
Ive tried various combinations of INDEX and MATCHING with LOOKUPS and setting criteria to match. I have not yet been able to succeed in achieving the desired result as I cannot seem to lock out the columns I do not need and just look at the yellow highlighted cell entries per column per row.
Any suggestions would be very welcome
J
EE_Example.xlsx
I’m wondering if you could help or perhaps suggest a resolution to my current issue
I have a live ‘cube’ of data against which I have some target statistics set up.
Some of these statistics are derived from a calculation, per row (these are individual entries in the database), that looks at specific cells and if they are ‘completed’ or filled with data.
What I’m having problems with is creating a formula in Column BE (VBA?) that will look at each row and look at specific cells and if these cells are filled, then add 1 to the calculation up to a maximum of 23.
I have attached an extract from the actual sheet. The yellow highlighted columns are the columns that I wish to look at for conformity ie. the cells on each row within those columns are completed. All other columns I need disregarded as part of the calculation.
Ive put in a sample calculation in Column BE for the last two row entries just to show what the result Im trying to accomplish, with the cells on the last two row highlighted in green to show the missing data that would culminate in the resulting sum being displayed.
Ive tried various combinations of INDEX and MATCHING with LOOKUPS and setting criteria to match. I have not yet been able to succeed in achieving the desired result as I cannot seem to lock out the columns I do not need and just look at the yellow highlighted cell entries per column per row.
Any suggestions would be very welcome
J
EE_Example.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Pleased to help
ASKER
Cannot believe it was this simple !
Thank you for the quick response as always
You help is always invaluable
J