COUNT OR SUM OR IF?

[Guest]

I have a table as below
if the refdensity is less than 31 then i want to count the coresponding
conversion (0 to 5)
if the refdensity is between 31 and 65 i want to count the coresponding
conversion (0 to 5)
if the refdensity is greater than 65 i want to count the coresponding
conversion (0 to 5)


Refdensity Result CONVERSION
1 A 3
2 0 #N/A
3 0 #N/A
4 A 3
5 A 3
34 A 3
35 A 3
36 -B 2
37 A 3
65 NRC 0
66 A 3
67 A 3
68 NRC 0
100 A 3
101 A 3
103 #N/A
104 #N/A
#N/A

I've tried sumif countif ifand but can't seem to crack it
thanks

 

[Bob Phillips]

The #N/A confuse, where they exactly reside

=SUM(IF((A2:A200<31)*(ISNUMBER(C2:C200)),C2:C200))

=SUM(IF((A2:A200>=31)*(A2:A200<=65)*(ISNUMBER(C2:C200)),C2:C200))

=SUM(IF((A2:A200>65)*(ISNUMBER(C2:C200)),C2:C200))

all array formulae, so commit with Ctrl-Shift-Enter

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

If you want only to "count" the total number of times each condition exists,
try these
=COUNTIF(A:A,"<31")

=COUNTIF(A:A,">=31")-COUNTIF(A:A,">=65")

=COUNTIF(A:A,">65")

If you actually want something different, a little more explanation would be
appreciated.

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

HI
I want to add the totals of the counts in column 'result'
therefore find it and add it at the same time
is that possible?

 

[Guest]

I'm afraid I'm still confuzed as to what you want. Bob already gave you the
formulas to sum the conversion column. The Result column appears to be TEXT,
which of course cannot be "summed"..........please try to give examples of
what you want as a result..........

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

HI
same thing different format

Fibreden Mean A.D / E.S.D
71.6 40.7 2.98
131.3 103.6 1.20
37.8 21.3 2.62
23.9 30.7 -0.82
63.0 50.6 1.00

ok, using the countif to group them and count how many fit the criteria.
then for each one that conforms to the criteria i want to total the ADESD
so that is for each criteria group what is the total (sum) of the ADESDs

Bob's worked well on the first sheet, I then transfered to the next type and
can't get answers keeps coming up with #NA
=SUM(IF((Mean<10)*(ISNUMBER(ADESD)),ADESD))

 

[Bob Phillips]

I presume that Mean and ADESD are named ranges> Are you sure they are
defined the same size?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

CHEERS BOB
yep different sizes
you learn somthing new each day