Countif across ranges question.

[Mathew P Bennett]

Good Evening All,
Any assistance on this would be gratefully received.
I have a sample worksheet as follows;
Coulmn A is 'text', B,is 'number', formula driven from data source in
another worksheet.

A B
1 X 1
2 Y 1
3 Z
4 Y 1
5 X 1
6 X 1
7 X
8 Y 1

I would like the function to count the number of X's in Column A, only if
there is a value in the corresponding cell in Column B.
ie. In this instance: Count should produce X = 3, Y = 3, Z = 0 in another
range.
NB. The 'blank cells' B3 & B7 are not actually 'blank', but hold formulae.

Any help & sugestions most welcome
Cheers,
Mathew

 

[Dan E]

Mathew,

For your example
To count the X's
=SUMPRODUCT(($A$1:$A$8="X")*(ISBLANK($B$1:$B$8) = FALSE))
To count the Y's
=SUMPRODUCT(($A$1:$A$8="Y")*(ISBLANK($B$1:$B$8) = FALSE))
To count the Z's
=SUMPRODUCT(($A$1:$A$8="Z")*(ISBLANK($B$1:$B$8) = FALSE))

Note: counts if ANY value is in column B (doesn't have to be numeric)
also, watch out for text wrapping in the formulas

Dan E