Countif

[Rik Smith]

Hi Guys,

I have attached a file, i would like to count column D values that are greater than zero, but only if column A or B or C is blank.

Thanks

 

[Luke M]

If you just want the count:
=SUMPRODUCT((((A2:A13="")+(B2:B13="")+(C2:C13=""))>=1)*(D2:D13>0))

If you want the sum:
=SUMPRODUCT((((A2:A13="")+(B2:B13="")+(C2:C13=""))>=1)*(D2:D13>0)*D2:D13)

 

[Rik Smith]

If you just want the count:
=SUMPRODUCT((((A2:A13="")+(B2:B13="")+(C2:C13=""))>=1)*(D2:D13>0))

If you want the sum:
=SUMPRODUCT((((A2:A13="")+(B2:B13="")+(C2:C13=""))>=1)*(D2:D13>0)*D2:D13)

Thanks Luke

That did not work, the answer should be 3, i have highlighted the values that should be counted

 

[Luke M]

Ah. In your first post then, this:

but only if column A or B or C is blank.

should have been:
but only if column A and B and C is blank. Different type of logic, but thankfully easier to do.

=SUMPRODUCT((A2:A13="")*(B2:B13="")*(C2:C13="")*(D2:D13>0))

 

[Rik Smith]

Thanks Luke M...worked perfect