Solved: countif

[Ger]

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]

"EHBO" > "E5000", so they are also being counted.

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

[Ger]

It works. Its so simple if you know it.


Thanks.

Ger

[mikerickson]

You're welcome.

[Bob Phillips]

=SUMPRODUCT(COUNTIF(range,{"E5*","E6*","E7*","E8*","E9*"}))

[Ger]

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]

You could use this array formula

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

[Ger]

=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