SUMPRODUCT Blank Cells with LEFT & RIGHT

[Unlucky-Phase]

Hello,


I'm looking to use this formula =SUMPRODUCT(LEFT(G3:G100,2)+(RIGHT(G3:G100,8))) with cells that have numbers in but some in the middle are blank. If I try and return it at the moment, I get a value error due to blank cells. How do I get it to skip blanks?


I don't want to shorten the column as I want to be able to insert new information everyday and the cell range to be long enough to always add up the numbers without adjusting the range.

To give you an idea, these are the type of numbers I'm using in the cells:
00:00:05:11
00:00:01:41
00:00:13:43
00:01:13:32

I'm currently unable to upload a worksheet.



Thanks,

 

[DanteAmor]

Welcome to the forum!

Try this array formula:

{=SUMPRODUCT(LEFT(IF(G3:G100<>"",G3:G100,0),2)+(RIGHT(IF(G3:G100<>"",G3:G100,0),8)))}

Or this shorter array formula

{=SUM(IF(G3:G100<>"",LEFT(G3:G100,2)+(RIGHT(G3:G100,8))))}

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[Rick Rothstein]

Here is another formula (normally entered) that you can consider...

=SUMPRODUCT(LEFT(G3:G100&"00",2)+RIGHT("00:00:00"&G3:G100,8))

 

[Unlucky-Phase]

Thank you both very much! All worked perfectly!
Have been trying to figure this out and just couldn’t get my head around it! Thanks again!

 

[Fluff]

@mollypj
For future reference, whilst we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

https://www.excelforum.com/excel-ge...nk-cells-with-left-and-right.html#post5197020

 

[Unlucky-Phase]

Hi Fluff,

No worries - sorry didn't realise and I hadn't had a correct answer hence why I was looking elsewhere for help. I'll make sure to reference these answers in the other thread.

Thanks

 

[DanteAmor]

Thank you both very much! All worked perfectly!
Have been trying to figure this out and just couldn’t get my head around it! Thanks again!

I'm glad to help you. Thanks for the feedback.