+ Reply to Thread
Results 1 to 9 of 9

How to count several non-adjacent cells

  1. #1
    RMF
    Guest

    How to count several non-adjacent cells

    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)<0.03)+((A2,C2,E2)>0.7),1,0))

    = #VALUE!



  2. #2
    Pete
    Guest

    Re: How to count several non-adjacent cells

    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).

    Pete


  3. #3
    topola
    Guest

    Re: How to count several non-adjacent cells

    Hi, that's simple:

    Give ranges names (Insert>Name>Define)
    A1:G1=years
    A2:G2=values

    This formula should do the work:
    =SUM(IF(years=2004,IF(values<0.03,1,IF(values>0.7,1,0)),0))

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

    --
    Tomek Polak, http://vba.blog.onet.pl


  4. #4
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451
    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=2004),--(A2:F2>0.7))+SUMPRODUCT(--(A1:F1=2004),--(A2:F2<0.03))
    Last edited by Cutter; 12-23-2005 at 11:02 AM.

  5. #5
    Peo Sjoblom
    Guest

    Re: How to count several non-adjacent cells

    If it is always every other row for 2004 use

    =SUMPRODUCT((MOD(COLUMN(A2:F2),2)=1)*((A2:F2<0.03)+(A2:F2>0.7)))

    for 2005 use

    =SUMPRODUCT((MOD(COLUMN(A2:F2),2)=0)*((A2:F2<0.03)+(A2:F2>0.7)))


    --

    Regards,

    Peo Sjoblom

    "RMF" <RMF@discussions.microsoft.com> wrote in message
    news:3E853774-24CA-4C68-A482-AF2A267BDC98@microsoft.com...
    > 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)<0.03)+((A2,C2,E2)>0.7),1,0))
    >
    > = #VALUE!
    >
    >




  6. #6
    AndreaSykes
    Guest

    RE: How to count several non-adjacent cells

    Hi RMF,

    The If function returns a "text" value (i.e. True or False) and the plus
    sign in your formula is asking Excel to "sum" a "text" 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 >.03 and the other <.7
    returning a value of 1 then summing the two columns.

    Dont really consider myself an expert but HTH

    AndreaSykes

    "RMF" wrote:

    > 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)<0.03)+((A2,C2,E2)>0.7),1,0))
    >
    > = #VALUE!
    >
    >


  7. #7
    Peo Sjoblom
    Guest

    Re: How to count several non-adjacent cells

    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

    "AndreaSykes" <AndreaSykes@discussions.microsoft.com> wrote in message
    news:49419454-963F-4E30-AAF6-82FFF4C2D5B5@microsoft.com...
    > Hi RMF,
    >
    > The If function returns a "text" value (i.e. True or False) and the plus
    > sign in your formula is asking Excel to "sum" a "text" 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 >.03 and the other <.7
    > returning a value of 1 then summing the two columns.
    >
    > Dont really consider myself an expert but HTH
    >
    > AndreaSykes
    >
    > "RMF" wrote:
    >
    > > 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)<0.03)+((A2,C2,E2)>0.7),1,0))
    > >
    > > = #VALUE!
    > >
    > >




  8. #8
    RMF
    Guest

    Re: How to count several non-adjacent cells

    Thnks! Works fine!

    RMF

    "topola" wrote:

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


  9. #9
    Pete
    Guest

    Re: How to count several non-adjacent cells

    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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1