Multiple IF

  • Thread starter Thread starter Pirke
  • Start date Start date
P

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
 
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.
 
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)
 
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
 
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.
 
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)
 
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))))
Click to expand...
...

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)
 
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)
 
Bob Phillips [/i]
Because it doesn't matter? If it is an OR condition, >A1:BA1=A2:BA
Click to expand...
will return FALSE.
...
hgrove > said:
=SUMPRODUCT(--(A1:BA1=A2:BA2)*(ISBLANK(A1:BA1)
+ISBLANK(A2:BA2)=0))
Click to expand...

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
 
Back
Top