SUMPRODUCT + ISNUMBER(SEARCH) + Empty Cells

[BLUV]

I have the following calculation working for me where OctEffort = I2:I3324
and OctEnvironment = G2:G3324.

=SUMPRODUCT(ISNUMBER(SEARCH({"QA","qual"},(OctEnvironment)))*OctEffort)

This approach requires me to be mindful of empty cells and ensure the I and
G columns do not interrogate cells beyond row 3324. If I set OctEffort and
OctEnvironment to be I2:I5000 and G2:G5000 respectively, then my calculation
fails because of blank/empty cells. Can I full-proof my calculation to
ignore any empty cells it may find?

 

[Max]

.. Can I full-proof my calculation to ignore any empty cells it may find?

It's probably due to null strings/text returned/existing within OctEffort
(real blank cells pose no problem)

Try the array-entered SUM(IF(...)) alternative:
=SUM(IF(ISNUMBER(SEARCH({"QA","qual"},(OctEnvironment))),OctEffort))

Above tested lightly, works fine
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[Max]

p/s: "Array-enter" means to press CTRL+SHIFT+ENTER (CSE) to confirm the
formula, instead of just pressing ENTER. When it's done correctly, Excel will
wrap curly braces { } around the formula, which you can see inside the
formula bar. Do sight this as a visual check that the formula is correctly
array-entered. If you don't see the curlies, then it isn't array-entered, and
it won't return the correct results. Click inside the formula bar again, and
re-do the CSE. Re-check.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---

 

[BLUV]

That seemed to work just fine. Thank you very much for you help and time.

 

[Max]

Glad to hear. You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik