Count non-consecutive cells.....

[Zadig Galbaras]

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 )

 

[Frank Kabel]

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

 

[Aladin Akyurek]

=SUMPRODUCT(--(MOD(COLUMN($B$1:$N$1)-CELL("Col",$B$1)+0,2)=0),--($B$1:$N$1=A1))"Zadig Galbaras" <[email protected]> wrote in messageHi!>> 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>>>

 

[Zadig Galbaras]

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)+COUNT.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?

 

[Frank Kabel]

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

 

[Aladin Akyurek]

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

 

[Frank Kabel]

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

 

[Aladin Akyurek]

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