PDA

View Full Version : Solved: countif



Ger
02-06-2008, 07:23 AM
I use Excell 2003
I use the formula countif(range;value) to get the number of items in a range. The range contains letters (C, P, EHBO, Res etc) and also it can contain a value starting with an E and a number between 5000 and 9000.
If i use the formula countif(range;">=E5000") i get a value 4 but there is only 1 value.
What goes wrong?

Ger

mikerickson
02-06-2008, 07:31 AM
"EHBO" > "E5000", so they are also being counted.

You could try
=COUNTIF(range,"E5*")+COUNTIF(range,"E6*")+COUNTIF(range,"E7*")+COUNTIF(range,"E8*")+COUNTIF(range,"E9*")

Ger
02-06-2008, 07:36 AM
It works. Its so simple if you know it.


Thanks.

Ger

mikerickson
02-06-2008, 07:36 AM
You're welcome.

Bob Phillips
02-06-2008, 08:54 AM
=SUMPRODUCT(COUNTIF(range,{"E5*","E6*","E7*","E8*","E9*"}))

Ger
02-07-2008, 12:24 AM
Is it possible to exclude a value in the countif formula.

example: I have the value POC, PE and P+1 to 3 numbers (P1, P142 etc)

If i use the formula =countif(range,"P*") the value POC and PE are also counted, but I need only the P+number.

I solved it now with the formula =countif(range,"P*")-countif(range,"POC")-countif(range,"PE").

Ger

Bob Phillips
02-07-2008, 01:03 AM
You could use this array formula

=SUM(IF(ISNUMBER(--MID(range,2,LEN(range)-1)),IF(LEFT(range,1)="P",1)))

Ger
02-07-2008, 02:07 AM
=Som(als(ISgetal(--deel(d7:d31,2,LENgte(d7:d31)-1)),als(Links(d7:d31,1)="P",1)))

I translated your formula. But I get an error on the first range (d7:d31)

Wrong translation???


Ger