Counting Question

  • Thread starter Thread starter JimS
  • Start date Start date
J

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

One possible way is to enter this formula : =SUM(IF
(A4:A19=B4:B19+1,1,0)) and CSE
 
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.
 
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.
 
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
 
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
 
Back
Top