Excel 2003 - COUNTA function counts cells with formula 1

[Divercem]

I am trying to get a count of rows (sorted by a size column) with a value greater than N15, in this case 700,000 (that's the easy part I'm using =COUNT(B2:B300,">" & N15) which returns 28). But I want to subtract any Exceptions (column E) that are checkmarked as a result of the following formula - =IF(AND(ISNA(MATCH(D2,K$10:K$14,0)),ISNA(MATCH(A2,K$18:K$44,0))),"","?") - Which compares the name & department against 2 lists in column K.

I tried subtracting COUNTA result from the COUNTIF like this =COUNTIF(B2:B300,">" & N15)- COUNTA(INDIRECT("E2:E" & COUNTIF(B2:B300,">" & N15) + 1,TRUE)). But the total is 0 because both functions return 28.

I'm sorry, I don't know how to post the spreadsheet to make it clearer.

 

[SkipVought]

Hi,

{...count of rows (sorted by a size column) with a value greater than N15..."

How about COUNTIF?

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[Divercem]

Thanks for the reply,

Oh, I see that in my first paragraph, I incorrectly said I'm using =COUNT(B2:B300,">" & N15). I apparently left the IF off when I typed it, but in paragraph 2 I cut and pasted the whole formula which is using CountIF.

I'm using the value of the size column in the CountIF function, my problem is there are exceptions that have to be excluded by the formula and there in lies the problem. How do I tell CountIF to count the row if the value of the size column is greater than the value of cell N15 BUT do not count the rows that are checkmarked in the exception column by the formula for that cell?

 

[SkipVought]

Count the exceptions and subtract them out.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[Bong]

Why isn't the number you want the result of countif(your stuff with N15 and columnB) [red]minus[/red] countif(whatever with columnE)?

_________________
Bob Rashkin

 

[SkipVought]

How about posting a relevant example of what you are counting and what you are excluding-- coupla columns of values, OK?

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[Divercem]

Thanks, I figured out what you meant and was going to recant my first reply, but you already replies with what I finally figures out. You were right...use CountIF to count the exceptions and subtract them out.

Can't see the forest for the trees.

The information I'm running this against is Exchange mailbox sizes. Any suggestions on automating this spreadsheet to read the information directly from Exchange? Such as UserName, mailbox size, # of items?

 

[SkipVought]

I don't but you might want to post THAT question in a new thread.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}