SUMPRODUCT +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?

 

[Larry Linson]

Chances are, you will stand a much better chance of getting a good answer to
your Excel question if you go back to the online interface (or even better
sign up at "news.microsoft.com" and read the newsgroup offline, too) and
scroll until you find an Excel newsgroup. This newsgroup is for discussion
of, and questions and answers about, Microsoft Access database software. It
is not just "general access to Microsoft".

Larry Linson
Microsoft Office Access MVP

 

[BLUV]

Thanks Larry. This was the first time I've used this community. And
wouldn't ya know I already made a mistake.

Thanks again.

 

[Larry Linson]

Thanks Larry. This was the first time I've used this community.

When you want to talk about Access, come back and visit again. We love to
talk about Access.

It is trivially easy to be misled by that online user interface, so don't
"feel lonely".

Larry Linson
Microsoft Office Access MVP