F
FreddieP
Hi I want to count the number of empty cells in column d if column a=x
what formula should I use then?
what formula should I use then?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Don Guillett
try
=SUMPRODUCT(--(a2:a22="x")*--(TRIM(d2:d22)=""))
=SUMPRODUCT(--(a2:a22="x")*--(TRIM(d2:d22)=""))
D
David Biddulph
Do you need the double unary minus if you use * for multiplication, Don?
D
Don Guillett
All seem to give the same ans.
=SUMPRODUCT((a2:a22="x")*(TRIM(d2:d22)=""))
=SUMPRODUCT(--(a2:a22="x")*--(TRIM(d2:d22)=""))
=SUMPRODUCT(--(a2:a22="x"),--(TRIM(d2:d22)=""))
=SUMPRODUCT((a2:a22="x")*(TRIM(d2:d22)=""))
=SUMPRODUCT(--(a2:a22="x")*--(TRIM(d2:d22)=""))
=SUMPRODUCT(--(a2:a22="x"),--(TRIM(d2:d22)=""))
D
David Biddulph
The extra double unary minuses won't do any harm.
=SUMPRODUCT(--(a2:a22="x")*--(TRIM(d2:d22)=""))
is the same as saying
=SUMPRODUCT(-1*-1*(a2:a22="x")*-1*-1*(TRIM(d2:d22)=""))
=SUMPRODUCT(--(a2:a22="x")*--(TRIM(d2:d22)=""))
is the same as saying
=SUMPRODUCT(-1*-1*(a2:a22="x")*-1*-1*(TRIM(d2:d22)=""))