Count non-consecutive cells.....

Hi!

I have this spreadsheet looking kind alike this:

A1=X

B1=""
D1=X
F1=""
H1=X
J1=""
L1=X
N1=X
"" = empty

P1=?

How can I, in P1 count how many of the cells B1, D1, F1, H1, J1, L1 and N1
is identical with A1?`
Count or countif do not work on non-consecutive cells I think.
This is vital because the cells in-between, i.e. C1, E1, G1, I1, K1, and M1
is already in use for other purposes.
There is in total seven cells I want to check how many are identical with
A1.

As you experts out there already have found out, I am no expert :-))


--


Zadig Galbaras
A Perturbed Norwegian Agnostic

Hi
try
=SUMPRODUCT(--(B1:N1=A1),--(MOD(COLUMN(B1:N1),2)=0))

--
Regards
Frank Kabel
Frankfurt, Germany

"Zadig Galbaras" schrieb im Newsbeitrag
...
Hi!

I have this spreadsheet looking kind alike this:

A1=X

B1=""
D1=X
F1=""
H1=X
J1=""
L1=X
N1=X
"" = empty

P1=?

How can I, in P1 count how many of the cells B1, D1, F1, H1, J1, L1
and N1
is identical with A1?`
Count or countif do not work on non-consecutive cells I think.
This is vital because the cells in-between, i.e. C1, E1, G1, I1, K1,
and M1
is already in use for other purposes.
There is in total seven cells I want to check how many are identical
with
A1.

As you experts out there already have found out, I am no expert :-))


--


Zadig Galbaras
A Perturbed Norwegian Agnostic

=SUMPRODUCT(--(MOD(COLUMN($B$1:$N$1)-CELL("Col",$B$1)+0,2)=0),--($B$1:$N$1=A1))"Zadig Galbaras" wrote in .. . Hi! I have this spreadsheet looking kind alike this: A1=X B1="" D1=X F1="" H1=X J1="" L1=X N1=X "" = empty P1=? How can I, in P1 count how many of the cells B1, D1, F1, H1, J1, L1 and N1is identical with A1?` Count or countif do not work on non-consecutive cells I think. This is vital because the cells in-between, i.e. C1, E1, G1, I1, K1, andM1 is already in use for other purposes. There is in total seven cells I want to check how many are identical withA1. As you experts out there already have found out, I am no expert :-)) -- Zadig Galbaras A Perturbed Norwegian Agnostic

well thank you to both of you :-)

Nice solutions, but in my ignorance I made out an example with a fixed
number of cells in-between the important ones.
So your formula did work properly, and came up with the wrong answer....
I changed the divisor in the MOG function from 2 to 10 and voila it worked.

But the number which came up didn't do it :-(
Thst's my fault!!

So I did some brain work and came up with a working solution:

=IF(O33"";COUNT.IF(S33;O33)+COUNT.IF(AC33;O33)+C OUNT.IF(AM33;O33)+COUNT.IF(AW33;O33)+COUNT.IF(BG33 ;O33)+COUNT.IF(BQ33;O33)+COUNT.IF(CA33;O33);"")

Here O33 is teh cell containg the value all others are compared with.
So IF S33 is excatly the same as O33 then count it, if not, do not count it!


I know it's amateurish, but in my world i works :-)

Is there a better way to do this?

--


Zadig Galbaras
A Perturbed Norwegian Agnostic

Hi
try:
=SUMPRODUCT(--(S33:CA33=O33),--(MOD(COLUMN(S33:CA33)-19,10)=0))

--
Regards
Frank Kabel
Frankfurt, Germany

"Zadig Galbaras" schrieb im Newsbeitrag
...
well thank you to both of you :-)

Nice solutions, but in my ignorance I made out an example with a
fixed
number of cells in-between the important ones.
So your formula did work properly, and came up with the wrong
answer....
I changed the divisor in the MOG function from 2 to 10 and voila it
worked.

But the number which came up didn't do it :-(
Thst's my fault!!

So I did some brain work and came up with a working solution:


=IF(O33"";COUNT.IF(S33;O33)+COUNT.IF(AC33;O33)+C OUNT.IF(AM33;O33)+COU
NT.IF(AW33;O33)+COUNT.IF(BG33;O33)+COUNT.IF(BQ33;O 33)+COUNT.IF(CA33;O33
);"")

Here O33 is teh cell containg the value all others are compared with.
So IF S33 is excatly the same as O33 then count it, if not, do not
count it!


I know it's amateurish, but in my world i works :-)

Is there a better way to do this?

--


Zadig Galbaras
A Perturbed Norwegian Agnostic

Since the data cells you test are regularly spaced, you can still exploit
the formula I provided:

=IF(O33"",SUMPRODUCT(--(MOD(COLUMN($S$33:$CA$33)-CELL("Col",$S$33)+0,10)=0),--($S$33:$CA$33=O33))

"Zadig Galbaras" wrote in message
...
well thank you to both of you :-)

Nice solutions, but in my ignorance I made out an example with a fixed
number of cells in-between the important ones.
So your formula did work properly, and came up with the wrong answer....
I changed the divisor in the MOG function from 2 to 10 and voila it
worked.

But the number which came up didn't do it :-(
Thst's my fault!!

So I did some brain work and came up with a working solution:

=IF(O33"";COUNT.IF(S33;O33)+COUNT.IF(AC33;O33)+C OUNT.IF(AM33;O33)+COUNT.IF(AW33;O33)+COUNT.IF(BG33 ;O33)+COUNT.IF(BQ33;O33)+COUNT.IF(CA33;O33);"")

Here O33 is teh cell containg the value all others are compared with.
So IF S33 is excatly the same as O33 then count it, if not, do not count
it!


I know it's amateurish, but in my world i works :-)

Is there a better way to do this?

--


Zadig Galbaras
A Perturbed Norwegian Agnostic

Hi Aladin
just a curious question: Why add '0' in your formula?

--
Regards
Frank Kabel
Frankfurt, Germany

"Aladin Akyurek" schrieb im Newsbeitrag
...
Since the data cells you test are regularly spaced, you can still
exploit
the formula I provided:


=IF(O33"",SUMPRODUCT(--(MOD(COLUMN($S$33:$CA$33)-CELL("Col",$S$33)+0,
10)=0),--($S$33:$CA$33=O33))

If you set that value to 1, it will ignore the first cell of the range of
interest.

"Frank Kabel" wrote in message
...
Hi Aladin
just a curious question: Why add '0' in your formula?

--
Regards
Frank Kabel
Frankfurt, Germany

"Aladin Akyurek" schrieb im Newsbeitrag
...
Since the data cells you test are regularly spaced, you can still
exploit
the formula I provided:


=IF(O33"",SUMPRODUCT(--(MOD(COLUMN($S$33:$CA$33)-CELL("Col",$S$33)+0,
10)=0),--($S$33:$CA$33=O33))