Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jan 2004
    Location
    Rochester, New Hampshire, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel-Counting from mulitple cells. (2007)

    Hi all,
    I'm looking to get a count if the cell contains the letter A. Now the cells that the formula needs to look in are like ever 5th cell IE (b5, f5, j5). Now I've defined the group of cells "absent). When I use the formula =countif(absent,"A") I get the return of #value. Any help would be great.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel-Counting from mulitple cells. (2007)

    It seems to me you mean every 4th cell in row 5, starting at B5.

    Functions such as COUNTIF don't work correctly with a non-contiguous range. Try this formula:

    =SUMPRODUCT((MOD(COLUMN(5:5),4)=2)*ISNUMBER(SEARCH ("A",5:5)))

  3. #3
    Lounger
    Join Date
    Jan 2004
    Location
    Rochester, New Hampshire, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel-Counting from mulitple cells. (2007)

    Hanz,
    I think we are close but I don't think it is working. The information I'm looking at is in rows not columns. So I have a question in the formula =SUMPRODUCT((MOD(COLUMN(5:5),4)=2)*ISNUMBER(SEARCH ("A",5:5)))
    You have Column should it be row? Second, a clear up on my part. The next cell I want to count is 7 away not four. So the cells I'm looking at start at I3, O3, U3, AA3 etc. Third, how would I edit for the next row down. IE I4, O4, U4 etc. Thanks for your help.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel-Counting from mulitple cells. (2007)

    COLUMN was intended that way - since you're counting cells in a row, we look at their column number.
    The 5:5 is the row - you originally mentioned cells in row 5. For row 3 you'd use 3:3 etc.
    For cells 7 columns apart, you'd replace the 4 in the formula with 7. But your example is inconsistent: I3 is 7 columns to the right of B3, but O3 is only 6 columns to the right of I3. If you meant B3, I3, P3, W3, you'd use

    =SUMPRODUCT((MOD(COLUMN(3:3),7)=2)*ISNUMBER(SEARCH ("A",3:3)))

    But if you meant C3, I3, O3, U3, you'd use

    =SUMPRODUCT((MOD(COLUMN(3:3),6)=3)*ISNUMBER(SEARCH ("A",3:3)))

    If you fill down the formula to the next row(s), the 3:3 will automatically be adjusted to 4:4 for row 4, and to 5:5 for row 5 etc.

  5. #5
    Lounger
    Join Date
    Jan 2004
    Location
    Rochester, New Hampshire, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel-Counting from mulitple cells. (2007)

    Hans,
    Thanks for the explanation of the formula and sorry I was all over the place with mine. The cells are every six starting at I3. The last fix I need help with is now I'm getting a circular reference error with it as I said it need to start counting at I3 and it needs to end by DY3. Now for the quick fix I went to excel options and I have the iterative calculation enabled and this stop the error. But I worried that this will effect other spread sheets I do. Do I put a letter reference in with the (3:3)?? Thanks for the help.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel-Counting from mulitple cells. (2007)

    You can replace both occurrences of <code>3:3</code> in the formula with <code>$I3:$DY3</code>:
    <code>
    =SUMPRODUCT((MOD(COLUMN($I3:$DY3),6)=3)*ISNUMBER(S EARCH("A",$I3:$DY3)))
    </code>
    This will limit the range the formula looks at.

Posting Permissions

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