Sumproduct (I think?)

S

Sandy

I have two ranges A1:A100, B1:B100 each containing single digit numbers
typically between 3 and 8. (No blank cells.
I would like to count all the instances of each time row B contains a number
one less than that in the equivalent A cell. eg if A5 contains 6 and B5
contains 5 then that would count as one instance. The pairs have to be the
same numbers. further eg A10 containing 6 and B10 containing 5 would count
as a second instance but A12 containing 7 and B12 containing 6 would not. Am
I making sense?
TIA
Sandy
 
D

Dave F

I would create a helper column, say in Column C, with the following formula:
=IF(A1=B1+5,1,0) and fill that down, then sum column C. That sum would be
your count of rows for which the value in column A is one more than the value
in column C.

Dave
 
D

Dave F

I mean: That sum would be your count of rows for which the value in column A
is one more than the value in column B.
 
P

Pete_UK

Put these values in C1:C6 - 3, 4, 5, 6, 7, 8, then in D1 enter this
formula:

=SUMPRODUCT((A$1:A$100=C1)*(B$1:B$100=C1-1))

then copy the formula down into D2:D6. It will give you the count of
the number of instances where A is one more than B, for the values of
A given in column C.

Hope this helps.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Variable Sumproduct Range 6
Using a function to count cells based on multiple criteria 1
Sum of Numbers of a Rolling 6 month Period range 0
Using Sumproduct when some of the values are null 5
Sumproduct I think 1
Alpha sorting (with a twist...?) 3
SUMPRODUCT function for two arrays. Array 1 contains text 1
Enhance sumproduct to return unique employee code count 5

Top