Counting values with a non-adjacent range.
Posted by Ronda on January 11, 2001 3:38 PM
I am trying to count values "D", "E", and "7a" in a column, but I want to only look at every 11th row (each row is a different week, info repeats for a different person every 11 weeks). I tried with COUNTIF, but it counts the entire range H13:H476 and doesn't allow for non adjacent cells (that I can tell). We have tried also sumif with D13:D476 identifying the weeks 1-11, but can't use D, E, and 7a as the values. Also tried to substitute 1 for D, E, and 7a; but this was apparently too convoluted for the formula. By the way, if there is not a D, E, or 7a in the cell, there is a 0 since each cell has a formula to seek info from another spreadsheet.
I would appreciate any help possible. Thanks.
Posted by Mark W. on January 12, 2001 6:46 AM
If cells A1:A6 contain {"Field1";"D";"7a";"E";"X";"D"}
then {=SUM((MOD(ROW($A$2:$A$6)-ROW($A$2)+1,2)=0)*EXACT($A$2:$A$6,{"D","E","7a"}))}
will count every other row if it contains ("D","E","7a"} and result in 1.
The general solution is:
{=SUM((MOD(ROW(range)-ROW(1st_cell)+1,nth_row)=0)*EXACT(range,{"D","E","7a"}))}
Posted by Ronda on January 14, 2001 6:40 PM
Thank you for the help. I tried your formula as indicated below. The response I got in the cell is #VALUE!
Every cell the formula examines has a 0 from the link to the other workbook, but I don't see how that would affect the result. I would appreciate more feedback if you have time.
=SUM((MOD(ROW($K$13:$K$496)-ROW($K$13)+1,12)=0)*EXACT($K$13:$K$496,{"D","E","7a"}))
Thanks.
Posted by Aladin Akyurek on January 15, 2001 10:13 AM
Apply
=COUNTIF($K$13:$K$496,"#VALUE!")
to check whether the range itself contains any VALUE! errors.
Aladin
Posted by Ronda on January 15, 2001 12:54 PM
No, the COUNTIF sequence by Aladin resulted in 0, so none of the range contains errors. Any other suggestions?
Ronda
Posted by Aladin Akyurek on January 15, 2001 1:46 PM
: Thank you for the help. I tried your formula as indicated below. The response I got in the cell is #VALUE! : Every cell the formula examines has a 0 from the link to the other workbook, but I don't see how that would affect the result. I would appreciate more feedback if you have time.
Try the following array formula:
=SUM((MOD(ROW(k13:k496)-ROW(k13)+1,12)=0)*IF(ISERROR(k13:k496),0,EXACT(k13:k496,{"D","E","7a"})))
Aladin
Posted by Ronda on January 15, 2001 7:50 PM
I tried your formula as indicated below. The response I got in the cell is #VALUE! Ronda
I tried the new formula. Now, even though there is data (D, E, 7a) in some cells, I get a result of 0, instead of a count of the cells with data(D, E, 7a).
Thanks for the continued help
Ronda
Posted by Aladin Akyurek on January 15, 2001 10:19 PM
Ronda: I begin to suspect that you don't enter the formula as an array formula. To enter this formula, you need to hit CONTROL+SHIFT+ENTER at the same time.
Aladin
Posted by Ronda on January 16, 2001 5:33 AM
I tried your formula as indicated below. The response I got in the cell is #VALUE!
Now I have tried both the ISERROR version and the original version of the formula from Mark W. as an array and I am still getting a result of 0. There should be a count of 2, since K13 has a D in it, and K25 has an E in it. I appreciate any more help you can give.
Ronda
Posted by Aladin Akyurek on January 17, 2001 12:50 PM
: I tried your formula as indicated below. The response I got in the cell is #VALUE!
Ronda:
E-mail exchange made your situation much more clear.
The way you want to count the relevant entries in your data requires a small modification to Mark's array formula:
=SUM((MOD(ROW(K13:K67)-ROW()-11,11)=0)*EXACT(K13:K67,{"D","E","7a"}))
The formula must be placed in row 13 in an empty cell (in X13, for example). Hit control+shift+enter to enter it and copy down as far as needed.
Aladin
