How to calculate a Sum when its an empty cell?

I am using the following formula to calculate Project velocity (hours spent on project) using index and match to take a letter and give it a value. However, the only only way I could make it work was to put a "space" in all the empty cells and make the "space" a value of 0. Is there a way I can make this work with out the spaces?

=SUM(INDEX($N22:$N26,N(IF(1,MATCH(B3:B27,$M22:$M26,0)))))



Thank you

Try

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

You’ll need to clear all the "spaces".

See attached updated example.

Thanks for the rep.



If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Cell K28 formula

SUM

Formula:

Please Login or Register  to view this content.

This would work too:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

@wk9128: maybe B28?

@TMS your eyes are too sharp, Thanks for your help correcting me

Cell B28 formula , Drag down and across

Formula:

Please Login or Register  to view this content.

You're welcome.

I like the single formula approach