Multiple IF

[Pirke]

Hi,

Hope you can help me
If got several values that needs to be compared, and for
each hit I want to add 1 point to a total.

the way I do it now is: values to compare are in a colum,
in row 1 and 2
the formula in the totalcolum is then =If(A1=A2,1,0)+if
(B1=B2,1,0)+if(C1=C2,1,0) etc untill colummm BA
(Hope this is clear, else I can make an example)

Can this formula be written in an easier way?



thanks, Peter

 

[Guest]

Hi

Try something like this:
=SUMPRODUCT((A1:BA1=A2:BA2))
I can't test it, because my Excel is busy updating an ODBC query.! Let me
know how it goes.

 

[Guest]

Hi

Now I've tested it!! Use this:
=SUMPRODUCT(--(A1:BA1=A2:BA2))

 

[Bob Phillips]

You just MIGHT want to change slightly to stop it comparing and adding blank
cells

=SUMPRODUCT(--(A1:BA1=A2:BA2)*(NOT(ISBLANK(A1:BA1))))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Pirke]

Bob and Andy

thanks, this works (although I don't understand yet why it
works, what it exactly does),
Can you please explain why it does what it does?
f.e. What do the -- mean?

thanks, peter

 

[Guest]

Hi

The sumproduct 'asks' whether A1=A2, etc and these results are returned as
TRUE or FALSE. We can't do maths with boolean results like this so by using
a double minus sign at the beginning we are tricking Excel into thinking the
results are 1s or 0s.

 

[Bob Phillips]

Peter,

Actually, my amended version doesn't need the --, it can be written as

=SUMPRODUCT((A1:BA1=A2:BA2)*(NOT(ISBLANK(A1:BA1))))

or with -- in both conditions

=SUMPRODUCT(--(A1:BA1=A2:BA2),--(NOT(ISBLANK(A1:BA1))))

See

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Multiple Condition Tests (using SUMPRODUCT)

for an explanation

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[hgrove]

Bob Phillips wrote...

You just MIGHT want to change slightly to stop it comparing and
adding blank cells

=SUMPRODUCT(--(A1:BA1=A2:BA2)*(NOT(ISBLANK(A1:BA1))))

...

Good idea. Why limit it to checking only A1:BA1 and not A2:BA2?

=SUMPRODUCT(--(A1:BA1=A2:BA2)*(ISBLANK(A1:BA1)+ISBLANK(A2:BA2)=0)

 

[Bob Phillips]

Because it doesn't matter? If it is an OR condition, A1:BA1=A2:BA2 will
return FALSE.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[hgrove]

Bob Phillips [/i]

Because it doesn't matter? If it is an OR condition, >A1:BA1=A2:BA

will return FALSE.
...

=SUMPRODUCT(--(A1:BA1=A2:BA2)*(ISBLANK(A1:BA1)
+ISBLANK(A2:BA2)=0))

Consider a shorter example. A1:F2 contain (b = blank)

0 1 b 0 b 0
b 1 0 b b 0

=SUMPRODUCT(--(A1:F1=A2:F2),1-ISBLANK(A1:F1)) returns 4.

=SUMPRODUCT((A1:F1=A2:F2)*(ISBLANK(A1:F1)+ISBLANK(A2:F2)=0)) return
2.

Which do you consider the correct answer? Note that while expression
like (X+Y>0) are kludged 'OR' conditions, expressions like (X+Y=0) ar
'NAND' (NOT(X) AND NOT(Y)) conditions