'COUNTIF' and 'AND"
 

I have two columns of data
I want a total count (not sum) of the first column only if the corresponding
second column is not blank
--
Thanks

'COUNTIF' and 'AND"
 

Try:-

=SUMPRODUCT((A1:A5<"")*(B1:B5=""))

Extend A5 - B5 as required

Mike

"Jeff" wrote:

I have two columns of data
I want a total count (not sum) of the first column only if the corresponding
second column is not blank
--
Thanks

'COUNTIF' and 'AND"
 

The way you put it, you basically only want to count the non-blank
cells of 2nd col. If so, a generic solution involving SUMPRODUCT (and
not using one of the COUNTx functions):

=SUMPRODUCT(--(B1:B10<""))

If you want a COUNTIF on A:A (e.g. all the 5's where B:B is not blank,

=SUMPRODUCT((A1:A10=5)*(B1:B10<""))

Note: the -- in the first formula is to convert the TRUE/FALSE that
the expression produces into 1/0 so that they can be summed (logical
values are ignored by aggregate functions). In the second case
multiplication does the conversion thus the -- is not needed.

HTH
Kostis Vezerides



HTH
On Jul 6, 5:50 pm, Jeff wrote:
I have two columns of data
I want a total count (not sum) of the first column only if the corresponding
second column is not blank
--
Thanks

'COUNTIF' and 'AND"
 

I think
=SUMPRODUCT((A1:A5<"")*(B1:B5<""))
Jeff wanted second column to not be blank
"Mike H" wrote:

Try:-

=SUMPRODUCT((A1:A5<"")*(B1:B5=""))

Extend A5 - B5 as required

Mike

"Jeff" wrote:

I have two columns of data
I want a total count (not sum) of the first column only if the corresponding
second column is not blank
--
Thanks