Sumproduct??

[Sandy]

Hi
I have a range A1:S1 with a series of integers and a range A2:S2 with
another series of integers. How can I count all the instances where the
equivalent cell in the second range is equal to two less than that in the
first range.

eg
6 4 9 7 8 etc
4 3 7 8 7 etc

the above would count 2 - Columns A and C

Thanks in advance
Sandy

 

[Dave Peterson]

=SUMPRODUCT(--(A1:S1-A2:S2=2))

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Sandy Mann]

Try:

=SUMPRODUCT(--(A2:S2=(A1:S1-2)))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy]

Thanks guys,

Sandy
In Edinburgh, the current capital of Scotland
and the crowning glory of festivals