software

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
1A3
20#N/A
30#N/A
4A3
5A3
34A3
35A3
36-B2
37A3
65NRC0
66A3
67A3
68NRC0
100A3
101A3
103#N/A
104#N/A
#N/A

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

--
CHRISK

The #N/A confuse, where they exactly reside

=SUM(IF((A2:A200lt;31)*(ISNUMBER(C2:C200)),C2:C200))

=SUM(IF((A2:A200gt;=31)*(A2:A200lt;=65)*(ISNUMBER(C2:C 200)),C2:C200))

=SUM(IF((A2:A200gt;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)

quot;CHRIS Kquot; gt; wrote in message
...
gt; I have a table as below
gt; if the refdensity is less than 31 then i want to count the coresponding
gt; conversion (0 to 5)
gt; if the refdensity is between 31 and 65 i want to count the coresponding
gt; conversion (0 to 5)
gt; if the refdensity is greater than 65 i want to count the coresponding
gt; conversion (0 to 5)
gt;
gt;
gt; Refdensity Result CONVERSION
gt; 1 A 3
gt; 2 0 #N/A
gt; 3 0 #N/A
gt; 4 A 3
gt; 5 A 3
gt; 34 A 3
gt; 35 A 3
gt; 36 -B 2
gt; 37 A 3
gt; 65 NRC 0
gt; 66 A 3
gt; 67 A 3
gt; 68 NRC 0
gt; 100 A 3
gt; 101 A 3
gt; 103 #N/A
gt; 104 #N/A
gt; #N/A
gt;
gt; I've tried sumif countif ifand but can't seem to crack it
gt; thanks
gt;
gt; --
gt; CHRISK
If you want only to quot;countquot; the total number of times each condition exists,
try these
=COUNTIF(A:A,quot;lt;31quot;)

=COUNTIF(A:A,quot;gt;=31quot;)-COUNTIF(A:A,quot;gt;=65quot;)

=COUNTIF(A:A,quot;gt;65quot;)

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

Vaya con Dios,
Chuck, CABGx3
quot;CHRIS Kquot; wrote:

gt; I have a table as below
gt; if the refdensity is less than 31 then i want to count the coresponding
gt; conversion (0 to 5)
gt; if the refdensity is between 31 and 65 i want to count the coresponding
gt; conversion (0 to 5)
gt; if the refdensity is greater than 65 i want to count the coresponding
gt; conversion (0 to 5)
gt;
gt;
gt; Refdensity Result CONVERSION
gt; 1A3
gt; 20#N/A
gt; 30#N/A
gt; 4A3
gt; 5A3
gt; 34A3
gt; 35A3
gt; 36-B2
gt; 37A3
gt; 65NRC0
gt; 66A3
gt; 67A3
gt; 68NRC0
gt; 100A3
gt; 101A3
gt; 103#N/A
gt; 104#N/A
gt; #N/A
gt;
gt; I've tried sumif countif ifand but can't seem to crack it
gt; thanks
gt;
gt; --
gt; CHRISK

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?
--
CHRISKquot;CLRquot; wrote:

gt; If you want only to quot;countquot; the total number of times each condition exists,
gt; try these
gt; =COUNTIF(A:A,quot;lt;31quot;)
gt;
gt; =COUNTIF(A:A,quot;gt;=31quot;)-COUNTIF(A:A,quot;gt;=65quot;)
gt;
gt; =COUNTIF(A:A,quot;gt;65quot;)
gt;
gt; If you actually want something different, a little more explanation would be
gt; appreciated.
gt;
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt;
gt;
gt; quot;CHRIS Kquot; wrote:
gt;
gt; gt; I have a table as below
gt; gt; if the refdensity is less than 31 then i want to count the coresponding
gt; gt; conversion (0 to 5)
gt; gt; if the refdensity is between 31 and 65 i want to count the coresponding
gt; gt; conversion (0 to 5)
gt; gt; if the refdensity is greater than 65 i want to count the coresponding
gt; gt; conversion (0 to 5)
gt; gt;
gt; gt;
gt; gt; Refdensity Result CONVERSION
gt; gt; 1A3
gt; gt; 20#N/A
gt; gt; 30#N/A
gt; gt; 4A3
gt; gt; 5A3
gt; gt; 34A3
gt; gt; 35A3
gt; gt; 36-B2
gt; gt; 37A3
gt; gt; 65NRC0
gt; gt; 66A3
gt; gt; 67A3
gt; gt; 68NRC0
gt; gt; 100A3
gt; gt; 101A3
gt; gt; 103#N/A
gt; gt; 104#N/A
gt; gt; #N/A
gt; gt;
gt; gt; I've tried sumif countif ifand but can't seem to crack it
gt; gt; thanks
gt; gt;
gt; gt; --
gt; gt; CHRISK

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 quot;summedquot;..........please try to give examples of
what you want as a result..........

Vaya con Dios,
Chuck, CABGx3quot;CHRIS Kquot; wrote:

gt; HI
gt; I want to add the totals of the counts in column 'result'
gt; therefore find it and add it at the same time
gt; is that possible?
gt; --
gt; CHRISK
gt;
gt;
gt; quot;CLRquot; wrote:
gt;
gt; gt; If you want only to quot;countquot; the total number of times each condition exists,
gt; gt; try these
gt; gt; =COUNTIF(A:A,quot;lt;31quot;)
gt; gt;
gt; gt; =COUNTIF(A:A,quot;gt;=31quot;)-COUNTIF(A:A,quot;gt;=65quot;)
gt; gt;
gt; gt; =COUNTIF(A:A,quot;gt;65quot;)
gt; gt;
gt; gt; If you actually want something different, a little more explanation would be
gt; gt; appreciated.
gt; gt;
gt; gt; Vaya con Dios,
gt; gt; Chuck, CABGx3
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;CHRIS Kquot; wrote:
gt; gt;
gt; gt; gt; I have a table as below
gt; gt; gt; if the refdensity is less than 31 then i want to count the coresponding
gt; gt; gt; conversion (0 to 5)
gt; gt; gt; if the refdensity is between 31 and 65 i want to count the coresponding
gt; gt; gt; conversion (0 to 5)
gt; gt; gt; if the refdensity is greater than 65 i want to count the coresponding
gt; gt; gt; conversion (0 to 5)
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Refdensity Result CONVERSION
gt; gt; gt; 1A3
gt; gt; gt; 20#N/A
gt; gt; gt; 30#N/A
gt; gt; gt; 4A3
gt; gt; gt; 5A3
gt; gt; gt; 34A3
gt; gt; gt; 35A3
gt; gt; gt; 36-B2
gt; gt; gt; 37A3
gt; gt; gt; 65NRC0
gt; gt; gt; 66A3
gt; gt; gt; 67A3
gt; gt; gt; 68NRC0
gt; gt; gt; 100A3
gt; gt; gt; 101A3
gt; gt; gt; 103#N/A
gt; gt; gt; 104#N/A
gt; gt; gt; #N/A
gt; gt; gt;
gt; gt; gt; I've tried sumif countif ifand but can't seem to crack it
gt; gt; gt; thanks
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; CHRISK

HI
same thing different format

FibredenMeanA.D / E.S.D
71.640.72.98
131.3103.61.20
37.821.32.62
23.930.7-0.82
63.050.61.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((Meanlt;10)*(ISNUMBER(ADESD)),ADESD))
--
CHRISK
I presume that Mean and ADESD are named rangesgt; Are you sure they are
defined the same size?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;CHRIS Kquot; gt; wrote in message
news
gt; HI
gt; same thing different format
gt;
gt; Fibreden Mean A.D / E.S.D
gt; 71.6 40.7 2.98
gt; 131.3 103.6 1.20
gt; 37.8 21.3 2.62
gt; 23.9 30.7 -0.82
gt; 63.0 50.6 1.00
gt;
gt; ok, using the countif to group them and count how many fit the criteria.
gt; then for each one that conforms to the criteria i want to total the ADESD
gt; so that is for each criteria group what is the total (sum) of the ADESDs
gt;
gt; Bob's worked well on the first sheet, I then transfered to the next type
and
gt; can't get answers keeps coming up with #NA
gt; =SUM(IF((Meanlt;10)*(ISNUMBER(ADESD)),ADESD))
gt; --
gt; CHRISK
gt;
gt;
CHEERS BOB
yep different sizes
you learn somthing new each day
--
CHRISKquot;Bob Phillipsquot; wrote:

gt; I presume that Mean and ADESD are named rangesgt; Are you sure they are
gt; defined the same size?
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt;