Ignore blank cells in SUMPRODUCT

[OanaBC]

Hello,


How can i ignore the blank cells in this formula?

Please Login or Register  to view this content.

Attachment 765346

This formula calculate just cells with "/Co".

If i complete all cells the formula it's works. But i need without blank cells, because not all is completed.


Please help me!
Thank you!

[davsth]

First of all welcome

please attach a sample as per the yellow banner and I am sure your formula can be amended. Include what your expected answer is.

[OanaBC]

i want this formula to return to me the sum of co-hours, that is to say those with "/co" for the sample (H11:AL11). That is 4, but i want the formula to work even if there aren't all the cells filled in.

[AliGW]

Please attach a workbook, as requested.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

[OanaBC]

i'm loaded. thank you!

[JeteMc]

Assuming that you want cells AL12:AL13 to display zero, paste the following array entered formula** into cell AL11 and drag the fill handle down:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Let us know if you have any questions.

[OanaBC]

Thank you so much!!!!

[XLent]

in SUMPRODUCT terms, as you asked

=SUMPRODUCT((RIGHT(E12:AI12;3)="/co")*LEFT(0&E12:AI12;2))

i.e. prefix every cell with 0, pull left 2 chars -- so where blank you get 0, not null string, which you can coerce successfully without affecting result.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.