Counting
 

I have a column where I'm using IF statements to return a value from another
column if certain conditions are meet and "" otherwise. Previously I had
manually enter that information and then did a count of any cell in that
column, using COUNTA, that contain a value. But, since changing to an if
statement, COUNTA is counting every cell because every cell now has an if
statement in it. I want to count cells only when the if statement returns a
value other than "" (blank).

How do I do this?

Here's my data
ColA ColB
1A =if(right(A1,1)="A",A1,"")
2C =if(right(A1,1)="A",A1,"")
3D =if(right(A1,1)="A",A1,"")
4E =if(right(A1,1)="A",A1,"")
5W =if(right(A1,1)="A",A1,"")
=countA(b1:b5)

Counting
 

I should add that the IF statement will not necessarily return the same value
even though that is the case in my example.

"Mark" wrote:

I have a column where I'm using IF statements to return a value from another
column if certain conditions are meet and "" otherwise. Previously I had
manually enter that information and then did a count of any cell in that
column, using COUNTA, that contain a value. But, since changing to an if
statement, COUNTA is counting every cell because every cell now has an if
statement in it. I want to count cells only when the if statement returns a
value other than "" (blank).

How do I do this?

Here's my data
ColA ColB
1A =if(right(A1,1)="A",A1,"")
2C =if(right(A1,1)="A",A1,"")
3D =if(right(A1,1)="A",A1,"")
4E =if(right(A1,1)="A",A1,"")
5W =if(right(A1,1)="A",A1,"")
=countA(b1:b5)

Counting
 

Error in formula corrected.


ColA ColB
1A =if(right(A1,1)="A",A1,"")
2C =if(right(A2,1)="A",A1,"")
3D =if(right(A3,1)="A",A1,"")
4E =if(right(A4,1)="A",A1,"")
5W =if(right(A5,1)="A",A1,"")
=countA(b1:b5)



"Mark" wrote:

I have a column where I'm using IF statements to return a value from another
column if certain conditions are meet and "" otherwise. Previously I had
manually enter that information and then did a count of any cell in that
column, using COUNTA, that contain a value. But, since changing to an if
statement, COUNTA is counting every cell because every cell now has an if
statement in it. I want to count cells only when the if statement returns a
value other than "" (blank).

How do I do this?

Here's my data
ColA ColB
1A =if(right(A1,1)="A",A1,"")
2C =if(right(A1,1)="A",A1,"")
3D =if(right(A1,1)="A",A1,"")
4E =if(right(A1,1)="A",A1,"")
5W =if(right(A1,1)="A",A1,"")
=countA(b1:b5)

Counting
 

Mark

Use the countif function

=COUNTIF(B2:B6,A1)

Regards
Peter

"Mark" wrote:

Error in formula corrected.


ColA ColB
1A =if(right(A1,1)="A",A1,"")
2C =if(right(A2,1)="A",A1,"")
3D =if(right(A3,1)="A",A1,"")
4E =if(right(A4,1)="A",A1,"")
5W =if(right(A5,1)="A",A1,"")
=countA(b1:b5)

Counting
 

To sum count the number of matches use:

=SUM(--(RIGHT(A1:A5,1)="a"))

entered as an array formula by pressing and holding [Ctrl] & [Shift] while
you press [Enter]

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Mark" wrote in message
...
Error in formula corrected.


ColA ColB
1A =if(right(A1,1)="A",A1,"")
2C =if(right(A2,1)="A",A1,"")
3D =if(right(A3,1)="A",A1,"")
4E =if(right(A4,1)="A",A1,"")
5W =if(right(A5,1)="A",A1,"")
=countA(b1:b5)



"Mark" wrote:

I have a column where I'm using IF statements to return a value from
another
column if certain conditions are meet and "" otherwise. Previously I had
manually enter that information and then did a count of any cell in that
column, using COUNTA, that contain a value. But, since changing to an if
statement, COUNTA is counting every cell because every cell now has an if
statement in it. I want to count cells only when the if statement
returns a
value other than "" (blank).

How do I do this?

Here's my data
ColA ColB
1A =if(right(A1,1)="A",A1,"")
2C =if(right(A1,1)="A",A1,"")
3D =if(right(A1,1)="A",A1,"")
4E =if(right(A1,1)="A",A1,"")
5W =if(right(A1,1)="A",A1,"")
=countA(b1:b5)

Counting
 

=counta() will count cells with formulas--even those that evaluate to "".

One more if you're trying to count the number of values in A1:A5 that end with
A.

=countif(a1:a5,"*a")

Mark wrote:

Error in formula corrected.

ColA ColB
1A =if(right(A1,1)="A",A1,"")
2C =if(right(A2,1)="A",A1,"")
3D =if(right(A3,1)="A",A1,"")
4E =if(right(A4,1)="A",A1,"")
5W =if(right(A5,1)="A",A1,"")
=countA(b1:b5)


"Mark" wrote:

I have a column where I'm using IF statements to return a value from another
column if certain conditions are meet and "" otherwise. Previously I had
manually enter that information and then did a count of any cell in that
column, using COUNTA, that contain a value. But, since changing to an if
statement, COUNTA is counting every cell because every cell now has an if
statement in it. I want to count cells only when the if statement returns a
value other than "" (blank).

How do I do this?

Here's my data
ColA ColB
1A =if(right(A1,1)="A",A1,"")
2C =if(right(A1,1)="A",A1,"")
3D =if(right(A1,1)="A",A1,"")
4E =if(right(A1,1)="A",A1,"")
5W =if(right(A1,1)="A",A1,"")
=countA(b1:b5)



--

Dave Peterson

Counting
 

The problem is I used a very simple example. In reality I'm counted hundreds
of things, none of which are the same. It may be 1A, 4U, or "Fred", in that
column and I need to count how many values are returned based on my if
statement.

"Dave Peterson" wrote:

=counta() will count cells with formulas--even those that evaluate to "".

One more if you're trying to count the number of values in A1:A5 that end with
A.

=countif(a1:a5,"*a")

Mark wrote:

Error in formula corrected.

ColA ColB
1A =if(right(A1,1)="A",A1,"")
2C =if(right(A2,1)="A",A1,"")
3D =if(right(A3,1)="A",A1,"")
4E =if(right(A4,1)="A",A1,"")
5W =if(right(A5,1)="A",A1,"")
=countA(b1:b5)


"Mark" wrote:

I have a column where I'm using IF statements to return a value from another
column if certain conditions are meet and "" otherwise. Previously I had
manually enter that information and then did a count of any cell in that
column, using COUNTA, that contain a value. But, since changing to an if
statement, COUNTA is counting every cell because every cell now has an if
statement in it. I want to count cells only when the if statement returns a
value other than "" (blank).

How do I do this?

Here's my data
ColA ColB
1A =if(right(A1,1)="A",A1,"")
2C =if(right(A1,1)="A",A1,"")
3D =if(right(A1,1)="A",A1,"")
4E =if(right(A1,1)="A",A1,"")
5W =if(right(A1,1)="A",A1,"")
=countA(b1:b5)



--

Dave Peterson