Sumproduct??

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sandy - 05 Feb 2008 17:49 GMT

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

Reply to this Message

Dave Peterson - 05 Feb 2008 18:02 GMT

=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

> Hi
> I have a range A1:S1 with a series of integers and a range A2:S2 with
[quoted text clipped - 10 lines]
> Thanks in advance
> Sandy

Signature

Dave Peterson

Reply to this Message

Sandy Mann - 05 Feb 2008 18:12 GMT

Try:

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

Signature

HTH

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Hi
> I have a range A1:S1 with a series of integers and a range A2:S2 with
[quoted text clipped - 10 lines]
> Thanks in advance
> Sandy

Reply to this Message

Sandy - 05 Feb 2008 19:45 GMT

Thanks guys,

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

> Hi
> I have a range A1:S1 with a series of integers and a range A2:S2 with
[quoted text clipped - 10 lines]
> Thanks in advance
> Sandy

Reply to this Message