software

Would like your help for this one:

I have a large sheet with data sorted in columns split for 2004 and 2005
(see below). I want to count the numbers of cells for e.g. 2004 that are
below 3% or above 70% (answers should be 1), however the formula does not
work and I have no idea why.

Appreciate if a expert could have a look at this. hope I explained it good
enough

Thnks in advance, RMF

A B C D E F
1 2004 2005 2004 2005 2004 2005
2 40.00% 80.0% 90.0% 40.0% 50% 60%

=SUM(IF(((A2,C2,E2)lt;0.03) ((A2,C2,E2)gt;0.7),1,0))

= #VALUE!
It looks like this should be an array formula - click in the formula
bar as if you were going to edit it and press CTRL SHIFT and ENTER at
the same time. Curly brackets will appear around the formula (you can
not type these yourself).

PeteHi, that's simple:

Give ranges names (Insertgt;Namegt;Define)
A1:G1=years
A2:G2=values

This formula should do the work:
=SUM(IF(years=2004,IF(valueslt;0.03,1,IF(valuesgt;0.7, 1,0)),0))

This is an array formula i.e. use CTRL SHIFT ENTER when typed in.

--
Tomek Polak, vba.blog.onet.pl
You say you want to count the cells that match but your attempted
formula uses sum. 2 very different things. I'll assume you actually
want to count them so try this:

=SUMPRODUCT(--(A1:F1=2005),--(A2:F2gt;0.7)) SUMPRODUCT(--(A1:F1=2005),--(A2:F2lt;0.03))--
Cutter
------------------------------------------------------------------------
Cutter's Profile: www.excelforum.com/member.php...foamp;userid=9848
View this thread: www.excelforum.com/showthread...hreadid=495749If it is always every other row for 2004 use

=SUMPRODUCT((MOD(COLUMN(A2:F2),2)=1)*((A2:F2lt;0.03) (A2:F2gt;0.7)))

for 2005 use

=SUMPRODUCT((MOD(COLUMN(A2:F2),2)=0)*((A2:F2lt;0.03) (A2:F2gt;0.7)))--

Regards,

Peo Sjoblom

quot;RMFquot; gt; wrote in message
...
gt; Would like your help for this one:
gt;
gt; I have a large sheet with data sorted in columns split for 2004 and 2005
gt; (see below). I want to count the numbers of cells for e.g. 2004 that are
gt; below 3% or above 70% (answers should be 1), however the formula does not
gt; work and I have no idea why.
gt;
gt; Appreciate if a expert could have a look at this. hope I explained it good
gt; enough
gt;
gt; Thnks in advance, RMF
gt;
gt; A B C D E F
gt; 1 2004 2005 2004 2005 2004 2005
gt; 2 40.00% 80.0% 90.0% 40.0% 50% 60%
gt;
gt; =SUM(IF(((A2,C2,E2)lt;0.03) ((A2,C2,E2)gt;0.7),1,0))
gt;
gt; = #VALUE!
gt;
gt;
Hi RMF,

The If function returns a quot;textquot; value (i.e. True or False) and the plus
sign in your formula is asking Excel to quot;sumquot; a quot;textquot; value, therefore it
does not recognize it. From looking at your formula it looks like you want a
value of 1 to return in the cell if it meets the criteria and a value of 0 if
it doesn't. Assuming you will then sum the column to get the total number of
cells that meet the criteria. A very simple solution would be to do separate
If functions in two separate columns, one for gt;.03 and the other lt;.7
returning a value of 1 then summing the two columns.

Dont really consider myself an expert but HTH

AndreaSykes

quot;RMFquot; wrote:

gt; Would like your help for this one:
gt;
gt; I have a large sheet with data sorted in columns split for 2004 and 2005
gt; (see below). I want to count the numbers of cells for e.g. 2004 that are
gt; below 3% or above 70% (answers should be 1), however the formula does not
gt; work and I have no idea why.
gt;
gt; Appreciate if a expert could have a look at this. hope I explained it good
gt; enough
gt;
gt; Thnks in advance, RMF
gt;
gt; A B C D E F
gt; 1 2004 2005 2004 2005 2004 2005
gt; 2 40.00% 80.0% 90.0% 40.0% 50% 60%
gt;
gt; =SUM(IF(((A2,C2,E2)lt;0.03) ((A2,C2,E2)gt;0.7),1,0))
gt;
gt; = #VALUE!
gt;
gt;

There are several reasons that RMF's formula does not work but adding TRUE
or FALSE is not one of them, in fact calculations using TRUE or FALSE is the
whole idea behind array formulas--

Regards,

Peo Sjoblom

quot;AndreaSykesquot; gt; wrote in message
...
gt; Hi RMF,
gt;
gt; The If function returns a quot;textquot; value (i.e. True or False) and the plus
gt; sign in your formula is asking Excel to quot;sumquot; a quot;textquot; value, therefore it
gt; does not recognize it. From looking at your formula it looks like you
want a
gt; value of 1 to return in the cell if it meets the criteria and a value of 0
if
gt; it doesn't. Assuming you will then sum the column to get the total number
of
gt; cells that meet the criteria. A very simple solution would be to do
separate
gt; If functions in two separate columns, one for gt;.03 and the other lt;.7
gt; returning a value of 1 then summing the two columns.
gt;
gt; Dont really consider myself an expert but HTH
gt;
gt; AndreaSykes
gt;
gt; quot;RMFquot; wrote:
gt;
gt; gt; Would like your help for this one:
gt; gt;
gt; gt; I have a large sheet with data sorted in columns split for 2004 and 2005
gt; gt; (see below). I want to count the numbers of cells for e.g. 2004 that are
gt; gt; below 3% or above 70% (answers should be 1), however the formula does
not
gt; gt; work and I have no idea why.
gt; gt;
gt; gt; Appreciate if a expert could have a look at this. hope I explained it
good
gt; gt; enough
gt; gt;
gt; gt; Thnks in advance, RMF
gt; gt;
gt; gt; A B C D E F
gt; gt; 1 2004 2005 2004 2005 2004 2005
gt; gt; 2 40.00% 80.0% 90.0% 40.0% 50% 60%
gt; gt;
gt; gt; =SUM(IF(((A2,C2,E2)lt;0.03) ((A2,C2,E2)gt;0.7),1,0))
gt; gt;
gt; gt; = #VALUE!
gt; gt;
gt; gt;
Thnks! Works fine!

RMF

quot;topolaquot; wrote:

gt; Hi, that's simple:
gt;
gt; Give ranges names (Insertgt;Namegt;Define)
gt; A1:G1=years
gt; A2:G2=values
gt;
gt; This formula should do the work:
gt; =SUM(IF(years=2004,IF(valueslt;0.03,1,IF(valuesgt;0.7, 1,0)),0))
gt;
gt; This is an array formula i.e. use CTRL SHIFT ENTER when typed in.
gt;
gt; --
gt; Tomek Polak, vba.blog.onet.pl
gt;
gt;

That was my immediate reaction to the formula, but then I noticed that
the formula is adding either 1 or 0 so it is effectively counting.

Pete