COUNTIF adds1

[Shpendi]

Hi all
I 'm using a countif function that is based on 3 arguments.

=COUNTIF($I$2:$I$300,">=" & $B$3)/COUNTIF($I$2:$I$300,"="
& "open")+COUNTIF($C$2:$C$300,"=" & A14)

This countif function works except adds 1 to the expected
answer.
If I subtract 1 will this be unstable?


Any ideas please?
Thanks in advance
Shpendi

 

[Frank Kabel]

Hi
do you require that column I AND column C match your criteria
together?.

 

[Bernie Deitrick]

Shpendi,

That's not stable - in fact, that isn't how COUNTIF works. You are probably
getting a +1 because your first two terms happen to be equal.

Instead, you should probably be using a SUMPRODCUT formula - What is it
,exactly, that you want to do?

HTH,
Bernie
MS Excel MVP

 

[Shpendi]

We trying to count number of records that furfill the
following criteria.
Column I Contains dates, column C is a list of ethnic
codes,
we want to select all records in column I where the date
is >= a defined cell that contains a date
OR is blank.
For those records we then want to count all records that
have a specific ethnicity code in column C

We are using the following

=SUMPRODUCT(1*(I2:I300>=Start),1*(I2:I300=""))
=SUMPRODUCT((I2:I300>=Start)+(I2:I300=""))
=SUMPRODUCT((I2:I300>=Start)*(I2:I300=""))


{ start is the name of the defined cell which contains the
date of the first argument.}



Also tried a number COUNTIF functions didn't work

Thnaks
Shpendi

 

[Frank Kabel]

Hi
try:
=SUMPRODUCT(--((I2:I300>=Start)+(I2:I300="")>0),--(C2:C300="code"))

 

[Shpendi]

Thankyou so much Frank
That works fine!

1) I have a problem with the following statment, picks
up a worng number.
It is supposed to select records in column D2300 which,
before the START date and those in column I that are BLANK.
=SUMPRODUCT((D2300<=Start)*(I2:I300=""))

2) We have a numeric column we need to find the AVERAGE of
the records that are based on another selection.
The below selection argument works but, when the AVERAGE
Argument is added dosen't work.

=SUMPRODUCT(--((G2:G300>=Start)+(G2:G300="")>0),--(AVERAGE
(F2:F300)))
We are also going to have to do MIN and MAX, but i think
they would be the same as average?

Thankyou
Shpendi

 

[Frank Kabel]

Hi
1. what does not work for your first statement?. Maybe try:
=SUMPRODUCT((D2300<=Start)*(D2300<>"")*(I2:I300=""))

2. Try the following array entered formula (entered with
CTRL+SHIFT+ENTER):
=AVERAGE(IF((G2:G300>=Start)+(G2:G300=""),F2:F300))

and similar:
=MAX(IF((G2:G300>=Start)+(G2:G300=""),F2:F300))
or
=MIN(IF((G2:G300>=Start)+(G2:G300=""),F2:F300))

 

[Shpendi]

Hi
That didn't work. it's averaging the whole column not the
records that fall under the selection.
also tried this still didn't work
=AVERAGE(SUMPRODUCT((G2:G300>=Start)+(G2:G300=""),F2:F300))

Thanks Shpendi

 

[Frank Kabel]

Hi
have you array entered the formula?. It should work. What is the exact
formula you have tried?

 

[Shpendi]

Hi
I'm Using the following formula.
it's averaging the whole column not the records that fall
under the selection.
=AVERAGE(IF((G2:G300>=Start)+(G2:G300=""),F2:F300))

Also tried these 2.
=AVERAGE(SUMPRODUCT(--((G2:G300>=Start)+(G2:G300="")>0)))--
(F2:F300)
=AVERAGE(SUMPRODUCT((G2:G300>=Start)+(G2:G300=""),F2:F300))

Thanks
Shpendi

 

[Frank Kabel]

Hi
have you array entered this formula (with CTRL+SHIFT+ENTER)?