Count if/Sum if/ or Sum product

Hi everyone,

I have a question about a formula in excel 2007. People had gave a score to multiple questionnaires. The range is from 1 to 10.
The scores per person are given in one row and the person are divided by age in columns.

For example:

18 years old
person 1) 8 7 9 8 4 5
Person 2) 3 5 6 7 8 9
person 3) 3 5 6 9 9 10

Range: A2 / C4
20 years old
person 1) 8 7 9 8 4 5
Person 2) 3 5 6 7 8 9
person 3) 3 5 6 9 9 10

The Question: What's the formula to count how many times the scores occur together in a row, for example 7 and 8? And the range is fixed by age. For example the range for the formula 1 is A2 to C4 and the criteria are 7 and 8. So i want to count the criteria 7 and 8 for each row in range of A2 to C4.

Originally Posted by vlady

can you put those data in a real workbook and provide some example of the expected results.

I have attached the file Questionnaires.
And the question is, how many times the scores per person correlate in the column Age?

So for example: the first column the scores 7 and 8 correlate per person, 7 times in the first column.

But it counts the 7 and 8's separate. And has to count them together. So in the first column the 7 and the 8 combines 2 times

Formula:

Please Login or Register  to view this content.

Isn't this possible? It gives a value error. Look at the bold sections of the formula.
For example if the 7 is in first part of the the column a always have to check it and it takes a lot of time.

Thnx

But it isn't possible to use two the same ranges, is it? For example COUNTIFS(B2:J2,$K$1,B2:C2,$L$1)? That's why i still get a VALUE error while using the same range size.

COUNTIFS(B2:J2,$K$1,B2:J2,$L$1)

But if they are the same size and range you'll still get a value error. Because of the number 7 and 8 using the same cell. This isn't possible in this column/row and gives a value error. But what's the solution for the error?

Originally Posted by dredwolf

I'm really not sure what you are asking, the formula in your last post is try to count values that are equal to k1{7} and equal to l1{8}, which it cannot be equal to both

COUNTIFS(B2:J2,$K$1,C2:K2,$L$1) counts where the first cell (b2 say) is equal to k1, and the next cell(c2) is equal to l1

if you wanted to count where b2 was between k1 and l1 you could use something like this countif(B2:J2,">"&K$1,B2:J2,"<"&$L$1)

But what if i want to equal B2:J2 to 7 and B2:J2 to 8?

B2 J2
1 2 3 4 5 6
4 3 6 7 8 9

3 and 4 combines two times in this column.
If i use your formula COUNTIFS(B2:J2,$K$1,C2:K2,$L$1) it will only combine once. Because it doesn't count the 4 in the second row.

Look at the yellow outcomes. The combination "7 and 8" should be 1 in the bold sections, but it isn't.

The difference is in the formula counts the combination "7 and 8" next to each other. But i want it to count the combination per row.

It gets a count of 1, if there's an combination. But i need to know how many combinations of 7 & 8 there are in the array.

Thnx. But if i want to calculate per row for the age group the different combinations?

The best possibility is:

Formula:

Please Login or Register  to view this content.

It does work. Thank you for that.

But if I now need to calculate the entire column. I will have to add all the rows individually. Is this the best option?
Maar als ik nu het voor de hele column moet berekenen, dan moet ik het per rij optellen.

For example:
=MIN(countif($B2:J2,$K$1),countif($B2:J2,$L$1))+MIN(countif($B3:J3,$K$1),countif($B3:J3,$L$1)) etc.

I did that, but then it counts every combination possible in the column. And it should count per person (each row)
I find the easiest way to sum your formula for each row in the column of age.
=MIN(countif($B2:J2,$K$1),countif($B2:J2,$L$1))+MIN(countif($B3:J3,$K$1),countif($B3:J3,$L$1)) etc.

What do you think?

Look at the comment in the attachment.

After you looked at it, the question arises:

Is the easiest to count it for each column, to sum the outcome per row with your formula?

Thnx for your answer.

Just one more question. What does the function Min( in this formula? Why do you use it?