How to use Countifs with 2 Criteria and a lot of same size Ranges?

0
votes

I have been trying for quite some time now to write COUNTIF formulas which would return a count of True values by 2 Criteria in a lot of same size Ranges.

I have a list of numbers where every block of 6 numbers represents a week. All of the numbers in every block are different ranging from 1 to 50. I need a formula that returns a count of how many times any 2 given numbers were together in one block.

So far I have only worked out a primitive formula. As You can see I only manage to make it work by adding +6 to coordinates of range. 

=IF(AND(IF(COUNTIF(C2:C7;E2);1;0);IF(COUNTIF(C2:C7;E3);1;0));1;0)+IF(AND(IF(COUNTIF(C8:C13;E2);1;0);IF(COUNTIF(C8:C13;E3);1;0));1;0)+IF(AND(IF(COUNTIF(C14:C19;E2);1;0);IF(COUNTIF(C14:C19;E3);1;0));1;0)

Is there any way to sum separate IF's in one formula without manually adding IF+IF(+6)+IF(+6) ... etc. if a number of weeks is known (around 700)?

Is there a more simple method than my solution to count cells by 2 Criteriain a lot of same size Ranges?

1
excelvbaexcel-formula
So with in the block of 6 the numbers are unique no duplication of the 1 to 50? – Scott Craner
Yes, every number within a block of 6 numbers is unique. – Liutauras

1 Answers

0
votes

I can shorten your main formula but you will still need to add each section:

=(SUMPRODUCT(COUNTIF(C2:C7;E2:E3))=2) + (SUMPRODUCT(COUNTIF(C8:C13;E2:E3))=2) + ...