Results 1 to 6 of 6

20070615, 16:26 #1
 Join Date
 Jan 2004
 Location
 Rochester, New Hampshire, USA
 Posts
 27
 Thanks
 0
 Thanked 0 Times in 0 Posts
ExcelCounting 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.

20070615, 17:43 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: ExcelCounting 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 noncontiguous range. Try this formula:
=SUMPRODUCT((MOD(COLUMN(5:5),4)=2)*ISNUMBER(SEARCH ("A",5:5)))

20070801, 23:03 #3
 Join Date
 Jan 2004
 Location
 Rochester, New Hampshire, USA
 Posts
 27
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: ExcelCounting 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.

20070801, 23:15 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: ExcelCounting 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.

20070803, 11:53 #5
 Join Date
 Jan 2004
 Location
 Rochester, New Hampshire, USA
 Posts
 27
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: ExcelCounting 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.

20070803, 12:15 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: ExcelCounting 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.