subtotal count with an if

[robcarr]

Hi,

I need to count all records when I apply a filter, however I only need count numbers which are greater than 0, some records are entered as 0.00 and I need to not count these as it will cause inaccurate figures.

I am not sure if it is possible to count without counting 0.00, all other cells are either blank or have a number in it that I need to check.

I do not want to edit the sheet other than to enter the formula so I do not want to add extra columns on to this already complex spreadhseet.

thanks

Hope this is of use, Rob.

 

[GlennUK]

Are you filtering on this column of numbers? And you are saying that filtering greater than zero still shows 0.00?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[robcarr]

no i am using
=subtotal(2,a2:a600) = 3

2 are genuine (7.5 and 8.0) and 1 cell has 0.00 entered in a cell, I need the result to only show 2.

when a cell has 0.00 entered into it the record has been amended, i dont have time to change all 0.00 cells, so i was wondering if it is possible to use the subtotal with an if.

Hope this is of use, Rob.

 

[xlbo]

not as far as I know - that would require COUNTIF functionality within the subtotal feature - which it doesn;t have

Why not just add another filter criteria for > 0 ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[robcarr]

the filter is on the campaign it would mean then adding a filter too each day not really feasable.

oh well thanks for the input, didnt think it was possible, but you never know that is why i posted really.

Hope this is of use, Rob.

 

[AnotherHiggins]

My first thought was to add another filter, as Geoff suggested, but then I noticed this line:

->i dont have time to change all 0.00 cells

So changing all cells that contain "0.00" to empty cells would fix this, if not for the time of changing them?

It shouldn't take more than a couple of seconds....

Just select the column that contains these bothersome "0.00" values and use Edit > Replace (or the keyboard shortcut [Ctrl]+[H]), put [highlight]0[/highlight] in the Find What field and leave the Replace With field empty.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.