Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 --- |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine: ISNUMBER(SEARCH( and EXACT( ? | Excel Discussion (Misc queries) | |||
sumproduct including empty cells | Excel Discussion (Misc queries) | |||
Limit to nested IF(ISNUMBER(SEARCH)) functions? | Excel Worksheet Functions | |||
Sumproduct ... Empty Cells vs Spaces? | Excel Discussion (Misc queries) | |||
isnumber with sumproduct | Excel Worksheet Functions |