SUMPRODUCT + ISNUMBER(SEARCH) + Empty Cells
 

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

=SUMPRODUCT(ISNUMBER(SEARCH({"QA","qual"},(OctEnvi ronment)))*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?


--
RyGuy

SUMPRODUCT + ISNUMBER(SEARCH) + Empty Cells
 

.. 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"},(OctEnvironm ent))),OctEffort))

Above tested lightly, works fine
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"BLUV" wrote:
I have the following calculation working for me where OctEffort = I2:I3324
and OctEnvironment = G2:G3324.

=SUMPRODUCT(ISNUMBER(SEARCH({"QA","qual"},(OctEnvi ronment)))*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?

SUMPRODUCT + ISNUMBER(SEARCH) + Empty Cells
 

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
---

SUMPRODUCT + ISNUMBER(SEARCH) + Empty Cells
 

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


"Max" wrote:

.. 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"},(OctEnvironm ent))),OctEffort))

Above tested lightly, works fine
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"BLUV" wrote:
I have the following calculation working for me where OctEffort = I2:I3324
and OctEnvironment = G2:G3324.

=SUMPRODUCT(ISNUMBER(SEARCH({"QA","qual"},(OctEnvi ronment)))*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?

SUMPRODUCT + ISNUMBER(SEARCH) + Empty Cells
 

Glad to hear. You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"BLUV" wrote in message
...
That seemed to work just fine. Thank you very much for you help and time.
--
RyGuy