Count Number of Populated Cells containing formulas

[Nerocell]

Excel 2010

Sheet 1 contains range B12:B353 with names of people. Sheet 2 range B12:B353 contains a formula to extract the names from Sheet 1 if range D12:D353 contains a certain value:


=IF(Sheet1!D12:D354="A",(Sheet1!B12:B353)," ")


At the bottom of each column in Sheet 2 I am trying to get a total count of populated cells (a running count of names actually visible). I have inserted formula:


=COUNTA(B12:B353)


Which should count the number of cells with text in them, right? But I believe it's somehow counting the formula as text because I inserted the value of "A" in Sheet 1 Column D for 3 names. It returned a number of 331 at the bottom of my column in Sheet 2. First off, a return of 331 doesn’t make sense in any way (still scratching my head at this return. It should have returned a value of 3. Any help would be appreciated.


Thanks,

Nero</SPAN>

 

[Special-K99]

I doubt its counting the "A" in your formula.
Replace the A and space in your formula with 1 and 0.
Recalculate.
I bet the answer is still the same.

You say B12:B353 but in the formula you increase the value to 354.
=IF(Sheet1!D12:D354="A",(Sheet1!B12:B353)," ")
Is this 354 a typo?

You dont need the brackets
you should be able to simplify this to

=IF(Sheet1!D12:D354="A",Sheet1!B12:B353," ")

Presumably this is an array formula?

You'd be better off posting the file.

COUNTA counts the number of NON-EMPTY cells, this includes text AND numbers.

 

[pplstuff]

Why not try:
=countif(___RangeHere___,"<> ")

Essentially, countif not " "...

 

[Nerocell]

Thanks for the speedy replies. Special-K99, The Value "A" in the formula is working as intended. My question was how to get the count of cells with data in them at the bottom of each column in Sheet 2. I've attached my doc(with generic information). You can place a value of A, B, C, D or E in sheet 1 under column D titled "Group" It will transfer it to the appropriate Group Column in Sheet 2 (Now if I could just get it to align one right under the other instead of copying over to their exact location).

pplsstuff: I couldn't get that formula to work. But you're on track with what I'm trying to acheive.


Goal: To keep an accurate automated count of names at the bottom of each column in Sheet 2.

On the attached doc, Sheet 2, The bottom of column B is the only one with a formula in it. Once I get that down I will use it on the rest.


Okay, so I couldn't find the icon to attach the file. But once I find it, I will post it.

Nero