We help IT Professionals succeed at work.

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

166 Views
Last Modified: 2017-03-31
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
Comment
Watch Question

Group Finance Manager
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Jase AlexanderCompliance Manager

Author

Commented:
Wow Roy

Cannot believe it was this simple !

Thank you for the quick response as always

You help is always invaluable

J
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
Pleased to help

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.