Count empty cells with a specific term
 

Hi I want to count the number of empty cells in column d if column a=x


what formula should I use then?

Count empty cells with a specific term
 

try
=SUMPRODUCT(--(a2:a22="x")*--(TRIM(d2:d22)=""))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"FreddieP" wrote in message
...
Hi I want to count the number of empty cells in column d if column a=x


what formula should I use then?

Count empty cells with a specific term
 

Do you need the double unary minus if you use * for multiplication, Don?
--
David Biddulph

"Don Guillett" wrote in message
...
try
=SUMPRODUCT(--(a2:a22="x")*--(TRIM(d2:d22)=""))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"FreddieP" wrote in message
...
Hi I want to count the number of empty cells in column d if column a=x


what formula should I use then?

Count empty cells with a specific term
 

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)=""))



--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Do you need the double unary minus if you use * for multiplication, Don?
--
David Biddulph

"Don Guillett" wrote in message
...
try
=SUMPRODUCT(--(a2:a22="x")*--(TRIM(d2:d22)=""))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"FreddieP" wrote in message
...
Hi I want to count the number of empty cells in column d if column a=x


what formula should I use then?

Count empty cells with a specific term
 

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)=""))
--
David Biddulph

"Don Guillett" wrote in message
...
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)=""))
--
Don Guillett
Microsoft MVP Excel

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Do you need the double unary minus if you use * for multiplication, Don?
--
David Biddulph

"Don Guillett" wrote in message
...
try
=SUMPRODUCT(--(a2:a22="x")*--(TRIM(d2:d22)=""))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"FreddieP" wrote in message
...
Hi I want to count the number of empty cells in column d if column a=x


what formula should I use then?