Countifs and sumproduct with two criteria

My problem is that I use countifs and sumproduct function with 2 criteria <range and >range. But it always return to zero.. Please help.. Thanks

OMG! I so happy to find this thread. My problem for 2 days is now solved. Thank Benishiryo!!!

Thanks to Vlady as well..

Hi, Here my updated spread sheet. I got a few issues tho.. Thanks in advance..

It's array formula: You have to commit with Ctrl+Shift+Enter, not just Enter.

Good morning fella,

C67 did not have conditional formatting because you asked for the conditional format to look for values between 25 - 49 -> what about 49.1, 49.5, 49.9?
To change this Highlight C12 down to C301, go to conditional formatting and select 'Manage Rules' then you need to edit rule for the Orange & Yellow ones, and change the value from 49 to 49.99 and on the yellow one, change the value from 74 to 74.99.
That will sort the conditional format issue.

Hi galvin,
Solved the conditional formatting. OMG! You guys are awesome.
Just wondering if you can check the formula for C3:C6.. This counts the number of student in the range. It does not tally with the column . Column E starting from E13 is supposed to give the list of student that meet the criteria.. Please note there is a drop button in cell 10....Thanks in advance..

Hi galvin,
Gonna look to your formula now. Please disregard the above post.

Excellent! The problem in the countif cell C3:c6 are solved.. I'm learning a lot here. Thank you so very much...

No worries!
You should also work through your entire sheet and make sure all formulas are correct, i have found mulitple errors with the formulas entered.
For example,
C3 reads - C11:C302, C4 reads - C11:C301, C6 reads - C12:C303.
E12 contains a seriously long formula, that in all fairness is out of my league, BUT it contains a static range (signified by the $ sign) and is looking at C12:C96, for a start, you have data inside cell C97, and the range goes as far down as C301
The same can be said for F12, that contains a static range (again signified by the $ sign) and is only looking at B12:C96, yet the range goes down as far as C301.

I have a smaple of your sheet on my desktop and am looking at sorting it for you, but its worth you knowing the issues found so far

ok my friend, another change you need to make is
I15:I18 -> these values must also reflect the changes in conditional formatting - 24 must become 24.99, 49 must become 49.99, 74 must become 74.99

Hi Garvin,
The reason I have made the range for c3 to c11:c303 is because I will be adding 200 more entries. I want that the countifs formula updates when I add students and their score.

Hi Garvin,
I actually tried earlier to update the droplist in h15:h18 but it didn't work.. I love the idea of Vlady of droplist. It's cool - It's out of my comprehension as it's so complicated tho.. I just hope that it will yield the right result. I must have ruined it because I inserted some rows.

Hhhhmmmm, im not sure why, but i cant get the table to show the full qty.
Even when i change all marks to 90.79%, it wont show Student 1-9 in E:E.

ggrr

Guru's - Could i ask for a lil help lol?