troubleshooting Question

Excel Formula to create sum of number of cells filled per row per selective columns

Avatar of Jase Alexander
Jase AlexanderFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft ExcelMicrosoft Office
3 Comments1 Solution175 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Roy Cox
Group Finance Manager

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 3 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 3 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004