Inventory spreadhseet with Column A = multiple product models and column c =
Date received

Need formula to count each criteria in column A if column b is blank.
Need second formula if column B is non-blank.

Thanks!

Corrected (sorry)

Inventory spreadhseet with Column A = multiple product models and column c =
Date received

Need formula to count each criteria in column A if column c is blank.
Need second formula if column c is non-blank.

Thanks!

for counting blanks try countblank(range)
for non-blanks try counta(range) or totalcells -countblank(range)

--
Hope this helps
Martin Fishlock


"mhmra" wrote:

Corrected (sorry)

Inventory spreadhseet with Column A = multiple product models and column c =
Date received

Need formula to count each criteria in column A if column c is blank.
Need second formula if column c is non-blank.

Thanks!

=SUM(--(FREQUENCY(IF($C$1:$C$10<"",MATCH($A$1:$A$10,$A$1 :$A$10,0)),ROW(INDI
RECT("1:"&ROWS($A$1:$A$10))))0))

and

=SUM(--(FREQUENCY(IF($C$1:$C$10="",MATCH($A$1:$A$10,$A$1: $A$10,0)),ROW(INDIR
ECT("1:"&ROWS($A$1:$A$10))))0))

which arearray formulae, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Note the second will fail if you have blanks in column A

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"mhmra" wrote in message
...
Corrected (sorry)

Inventory spreadhseet with Column A = multiple product models and column c
=
Date received

Need formula to count each criteria in column A if column c is blank.
Need second formula if column c is non-blank.

Thanks!

Bob,

Thank you for your response. I was attempting to use a range of $A2:$A9999
(which would include blanks) in order to capture new information as it was
entered. Do array formulas update automatically? Is there a way to alter
the formula to handle blanks in column A?

Thanks!

"Bob Phillips" wrote:

=SUM(--(FREQUENCY(IF($C$1:$C$10<"",MATCH($A$1:$A$10,$A$1 :$A$10,0)),ROW(INDI
RECT("1:"&ROWS($A$1:$A$10))))0))

and

=SUM(--(FREQUENCY(IF($C$1:$C$10="",MATCH($A$1:$A$10,$A$1: $A$10,0)),ROW(INDIR
ECT("1:"&ROWS($A$1:$A$10))))0))

which arearray formulae, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Note the second will fail if you have blanks in column A

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"mhmra" wrote in message
...
Corrected (sorry)

Inventory spreadhseet with Column A = multiple product models and column c
=
Date received

Need formula to count each criteria in column A if column c is blank.
Need second formula if column c is non-blank.

Thanks!

Martin,

I've included a data sample below. I need a count of column A by each
unique entry when Column C is populated and a separate count when Column C is
blank.

Data sample:

Column A Column C
5100 11/27/06
5100
5100 11/28/06
2420 11/26/06
2420
2420 11/28/06

How would you conditionally nest your formulae together (countblank and
CountA)?

Thanks!

"Martin Fishlock" wrote:

for counting blanks try countblank(range)
for non-blanks try counta(range) or totalcells -countblank(range)

--
Hope this helps
Martin Fishlock


"mhmra" wrote:

Corrected (sorry)

Inventory spreadhseet with Column A = multiple product models and column c =
Date received

Need formula to count each criteria in column A if column c is blank.
Need second formula if column c is non-blank.

Thanks!

Try these array formulas:

=COUNT( 1 / ( MATCH( $A$1:$A$10, IF( ( $A$1:$A$10 < "" ) * ( $C$1:$C$10 <
"" ), $A$1:$A$10, #N/A ), 0 ) = ( ROW( $A$1:$A$10 ) - ROW( $A$1 ) + 1 ) ) )

and

=COUNT( 1 / ( MATCH( $A$1:$A$10, IF( ( $A$1:$A$10 < "" ) * ( $C$1:$C$10 =
"" ), $A$1:$A$10, #N/A ), 0 ) = ( ROW( $A$1:$A$10 ) - ROW( $A$1 ) + 1 ) ) )

--
Regards,
Luc.

"Festina Lente"


"mhmra" wrote:

Inventory spreadhseet with Column A = multiple product models and column c =
Date received

Need formula to count each criteria in column A if column b is blank.
Need second formula if column B is non-blank.

Thanks!