Link to home
Start Free TrialLog in
Avatar of Jase Alexander
Jase AlexanderFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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

Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jase Alexander


Wow Roy

Cannot believe it was this simple !

Thank you for the quick response as always

You help is always invaluable

Pleased to help