EXCEL: counta and cells with formulas

[dh42891]

Is there a function similar counta() that does not count cells with formulas in them? perhaps one that only considers the results of that formula? thanks,

dylan

 

[SkipVought]

Hi,

So what do you plan to do with this information?

Skip,

_[red][/red]

 

[dh42891]

I've got five 'chunks' of steps I'm trying to keep track of. Once everything in Chunk 1 is complete, those columns will be hidden and we'll move on to Chunk 2. But, I want to keep some summary information from Chunk 1 visible. So, normally the first column in Chunk 2 would indicate that someone has started Step 1 of Chunk 2. Until that happens, I want that same column to indicate that that row is ready to start Chunk 2. The way I had planned on doing this was with a simple =if(D1="yes","available",""). But, I want to keep a running percentage at the bottom of the column for Step 1 of Chunk 2 that indicates how many have been started. So, I was using counta() to do that. It, however, considers any and all values. I could use COUNTIF() but I have more than 7 possible values that would indicate Step 1 of Chunk 2 has been initialized. Does that answer your question clearly? Thanks,

dylan

 

[SkipVought]

Obviously you have a very complex process.

Does something need to happen between chunks?

You could make a function to return shcu a count. The main point is

Function CountNotFormula(rng as range)
  For Each c In rng
    With c
      If CStr(.Value) = .Formula Then
        CountNotFormula = CountNotFormula + 1
      End If
    End With
  Next
End Function

Skip,

_[red][/red]

 

[KenWright]

With your countif data in say A3:A3000, and your list of possible values in say G2:G12

=SUMPRODUCT(--($A$3:$A$30=TRANSPOSE($G$2:$G$12)))

array entered using CTRL+SHIFT+ENTER

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[dh42891]

No, nothing happens between chunks.

Thanks Ken, I will check into that shortly. I have to get the rest working before I add more bulk... Thanks for the help!

dylan

 

[KenWright]

Apologies, - for the ranges I gave the formula should have been:-

=SUMPRODUCT(--($A$3:$A$3000=TRANSPOSE($G$2:$G$12)))

array entered

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------