Condition formatting with four condition

[Closed]
Report
-
 Trowa -
Hello,
sir,
i have one problem as below-
name mar hin eng caste student's cat
a 17 15 17 sc girl
b 12 18 8 sc girl
c 5 10 20 sc boy
i want to know from the given information that is 1) How many sc girls got marks between 16 to 20 in mar subject ? (I want to know the formula in excel with four condition )

1 reply

Hi Prakash,

I arranged your data like this:
name	mar	hin	eng	caste	student's cat
a	17	15	17	sc	girl
b	12	18	8	sc	girl
c	5	10	20	sc	boy


This is put in the range A1:F4.

Now you would like to know how many rows/names has:
1. Mar is between 16 and 20
2. Caste is "sc"
3. Student's cat is "girl"

Then use this array formula in any cell:
=SUM(IF(B2:B4>=16,IF(B2:B4<=20,1,0),0)*(E2:E4="sc")*(F2:F4="girl"))
Confirm this formula by hitting Ctrl+Shift+Enter.

Change the range in the array formula to suit your situation.

I hope this is what you want, since your title is different from your query?!

Best regards,
Trowa