COUNTIF for variables that meets a dynamic criteria

Hi guys, i'm new to this forum. I seldom need to create excel spreadsheet, so I don't know how to solve this simple solution (which i could with spss).

I am looking for a non-VBA solution because not all my employees/ clients have VBA-access.

Problem

Column A: Group Identifier
Column B: Test 1 Score
Column C: Test 2 Score
Column D: G2 - Group required
Column E: E2 - Criteria for Test 1 (e.g. >=30)
Column F: F2 - Criteria for Test 2 (e.g. <=50)
Column G: G2 - Generated Results

I want to create a spreadsheet that allows the user to count the number of entries that meets a user-specified criteria, which they can key in on Column E and Column F.

The user will also be able to identify the group identifier (e.g. class A) for the counting.

Example the user wants to find out the number of people in class A that scores higher and equal to 30 for Test 1 AND/ OR score lower and equal to 50 for Test 2.

The user will key in "A" in cell G2, ">=30" in cell E2 and "<=50" in cell F2.

I tried using COUNTIFS function, but i can't make it work to allow the AND/ OR condition.
I tried using SUMPRODUCT, but sum product won't allow me to specify the operant (i.e. <=, >=). The direction of operant is important, because the user need to be able to specify the criteria.

I have added a sample excel sheet.

Thanks in advance!

Hi sixthsense, thanks for the response, but it is not working as I intended.

The criteria for the test Score 1 and 2 is supposed to be AND / OR

The formula you gave me is AND.

Whereas i need the formula to give me the responses for OR.

Example, if the criteria is >=30 for Test 1 and <=50 for Test 2

If the Test 1 and Test 2 scores are:
40 / 60
20 / 40
40 / 40

The 3 individuals would be counted to fit into the criteria because individual 1 has a test score 1 of higher than 30, individual 2 has a test score 2 of less than 50 and individual 3 has a test score 1 of higher than 30 and test score 2 of less than 50.

Originally Posted by :) Sixthsense :)

May be try like this...

Didn't work. This formula will double count an individual if he meets the criteria of test score 1 and test score 2.

Maybe countif is not the best way to do this? I tried another line of reasoning, by creating a new variable in Column H, using the IF function. Basically the function will return a value of 1 if the Column B values meets criteria in Column E or if Column C values meets criteria in Column F.

The problem is that the IF function does not support an INDIRECT function, so i can't link it to Column E and Column F values.

The sample is actually sufficient... so maybe its my explanation is unclear.

Ok, i have attached another sample, and then highlighted in red the individuals who should be flagged out by the criteria. The test scores which meet the criteria are also highlighted in red.

Based on the settings in the yellow box, the correct answer should be 5.

Thanks for persisting in helping me sixthsense! :D

When i used your formula, it gives me the answer 6. The correct answer ought to be 5.

That is because it counted each occurence of Test 1 meeting criteria, and Test 2 meeting criteria. It essentially double count the same individual, if his test score 1 meets the criteria and test score 2 meets the criteria.

In sample 2, you will see that the the individual in Row 2 has both tests meeting the criteria. But he is only 1 individual, and he should not be double counted.

What i want is counting the number of individuals, which means counting the number of red-coloured cell in column A.

That works benishyro! Thanks!

P.S. and i noticed you are from Singapore too!