Checking for a values in cells but counting if false

[Tez Draper]

Hi,

I am looking for a formula which looks at a range in sheet 2, if this range has not value then I want it to then count if a value ("athers01") is present in another range.

I want the result to return on sheet 1.

Any help would be great - thanks

 

[schielrn]

Welcome to the Board!

Maybe you want something like this:

In Excel 2007 and newer:

=countifs(sheet2!range1,"",sheet2!range2,"athers01")

or in any version:

=sumproduct((sheet2!range1="")*(sheet2!range2="athers01"))

Replace range1 with the correct range and range2 with the correct range. Also in the any version one, you cannot use whol column references.

Hope that helps.

 

[Tez Draper]

Fantastic - works a treat! Many thanks