# Counting a pair of values

1. ## Counting a pair of values

Hi everyone
I have 6 columns and 1000 Rows populated with numbers from 1 thru 41
What I'd like to do, is to count all consecutive rows matching same pair and put the results in a 41x41 matrix.

2. ## Re: Counting a pair of values

what have you got so far? Or are we supposed to mock it up for you?
follow the advice in the banner at the top of the post and upload a sample that represents what you've got AND what you would want to see.

3. ## Re: Counting a pair of values

Thank you Sambo Kid trying to help me...for instance In the attached file you can see the number 18 (row 5), was followed by number 5, (row6) ….8 times
all the way down until row 1004, the first one was in rows 5 and 6, the second one in rows 78 79. the last one was found in rows 880 and 881
the total count for the pair 18-5 was 8, this count will appears in the intersection of column z and row 9 need to do for all pairs (1 1, 1 2, 1 3....41 39, 41 40,
41 41 filling the matrix 41x41 with the results

4. ## Re: Counting a pair of values

it appears to me that a simple adjustment to your formula will get you what you want...
=SUMPRODUCT((\$A\$5:\$F\$1004=I\$4)*(\$A\$6:\$F\$1005=\$H5))+SUMPRODUCT((\$A\$5:\$F\$1004=I\$4)*(\$B\$6:\$B\$1005=\$H5))+SUMPRODUCT((\$A\$5:\$F\$1004=I\$4)*(\$D\$6:\$D\$1005=\$H5))+SUMPRODUCT((\$A\$5:\$F\$1004=I\$4)*(\$E\$6:\$E\$1005=\$H5))+SUMPRODUCT((\$A\$5:\$F\$1004=I\$4)*(\$F\$6:\$F\$1005=\$H5))
unless I am misunderstanding your requirements.

5. ## Re: Counting a pair of values

Thank you Mr Sambo KID, making some little adjustment works 100%,I'd like to ask you to finish totally my request, what do I need to do to adjust the range in the
formula indirectly, for example let insert a column with dates I want to apply the formula to calculate between dates which are in a1 and a2, check the new file..sheet2 is the old file and Dates is the new one with

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1