Counting Question

[JimS]

I'm trying to construct two formulas that do the following: In the two
columns, in the cells just under A and B, I want to count the number
of times the number in column A is exactly one greater than the number
in column B. I want to do the same for column B. I've put the
correct answers under A and B in parenethesis.

A B
(2) (3)

9 7
5 6
4 7
5 0
8 7
4 10
1 2
2 4
7 8
10 11
6 0
3 7
4 2
13 7
5 4
5 3

 

[RagDyeR]

I believe your answer in the B column should be 4, not 3!

Anyway, in A1:

=SUMPRODUCT(--(A2:A17-B2:B17=1))

And in B1:

=SUMPRODUCT(--(B2:B17-A2:A17=1))
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



I'm trying to construct two formulas that do the following: In the two
columns, in the cells just under A and B, I want to count the number
of times the number in column A is exactly one greater than the number
in column B. I want to do the same for column B. I've put the
correct answers under A and B in parenethesis.

A B
(2) (3)

9 7
5 6
4 7
5 0
8 7
4 10
1 2
2 4
7 8
10 11
6 0
3 7
4 2
13 7
5 4
5 3

 

[Dave Peterson]

Try:

=SUMPRODUCT(--(A2:A17-B2:B17=1))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=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

======
I got 4 for the version column B (b-a=1, right???)

 

[JoelS]

I'm trying to construct two formulas that do the following: In the two
columns, in the cells just under A and B, I want to count the number
of times the number in column A is exactly one greater than the number
in column B.  I want to do the same for column B.  I've put the
correct answers under A and B in parenethesis.

A            B
(2)        (3)

9       7
5       6
4       7
5       0
8       7
4       10
1       2
2       4
7       8
10      11
6       0
3       7
4       2
13      7
5       4
5       3

One possible way is to enter this formula : =SUM(IF
(A4:A19=B4:B19+1,1,0)) and CSE

 

[JimS]

Hi, thanks to all. Yes, column B should be 4. If I add data to the
columns will these formulas still work.

Also, using sumproduct, must you use the exact number of rows in your
formula? I noticed that if you go deeper (in anticipation of adding
more data later) sumproduct won't work. Sum if will.

Thanks again to all.

 

[Ragdyer]

TRUE, in *certain* configurations of arguments in Sumproduct, there might be
problems with empty ranges.
That's not the case with this formula.

=SUMPRODUCT(--(B2:B17-A2:A17=1))
works just as well as:
=SUMPRODUCT(--(B2:B17000-A2:A17000=1))

when you only have the first 16 rows populated.

 

[JimS]

Thanks, you guys. I've always had some problems with sumproduct and
empty ranges, but it appears I can apply this concept to other
spreadsheets as well.

Thanks

 

[RagDyeR]

Appreciate the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Thanks, you guys. I've always had some problems with sumproduct and
empty ranges, but it appears I can apply this concept to other
spreadsheets as well.

Thanks