Count function-conditions

  • Thread starter Thread starter gxrxba
  • Start date Start date
G

gxrxba

=SUMPRODUCT(($I$104:$I$9998="CH")*($R$104:$R$9998<>0))

In the example above, I am trying to count the values in the R column
if values in the I column=CH. Now I would like to add an additiona
condition in the R column, ie I would only like to count the values i


1. <> 0 (already exiting condition) AND
2. Values which are outside of the range from -999 to 999. (ie if th
value in the r column is between -999 to 999, it should not b
counted)

Any suggestions.

Thanks in advance
 
=SUMPRODUCT(($I$104:$I$9998="CH")*(($R$104:$R$9998>999)+($R$104:$R$9998<-999
))


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi,

Try,

=SUMPRODUCT(($I$104:$I$9998="CH")*($R$104:$R$9998<>0)*($R$104:$R$9998<=-9
99)*($R$104:$R$9998>=999))

Hope this helps!
 
Sorry! My mistake! As Bob posted, the correct formula is:

=SUMPRODUCT(($I$104:$I$9998="CH")*(($R$104:$R$9998>999)+($R$104:$R$9998<-
999
))
 
=SUMPRODUCT(($I$104:$I$9998="CH")*(($R$104:$R$9998>999)+($R$104:$R$9998<-999))
Click to expand...
...

In general I prefer as few function calls as possible. However, in this case

=SUMPRODUCT(($I$104:$I$9998="CH")*(ABS($R$104:$R$9998)>999))

may be more efficient.
 
Harlan Grove said:
...

=SUMPRODUCT(($I$104:$I$9998="CH")*(($R$104:$R$9998>999)+($R$104:$R$9998<-99 9))
..

In general I prefer as few function calls as possible. >
Click to expand...
I never knew that<VBG>!
 
Back
Top