COUNTIF adds1

  • Thread starter Thread starter Shpendi
  • Start date Start date
S

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
 
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
 
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
 
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 D2:D300 which,
before the START date and those in column I that are BLANK.
=SUMPRODUCT((D2:D300<=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
 
Hi
1. what does not work for your first statement?. Maybe try:
=SUMPRODUCT((D2:D300<=Start)*(D2:D300<>"")*(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))
 
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
 
Hi
have you array entered the formula?. It should work. What is the exact
formula you have tried?
 
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
 
Back
Top