Problems with Count IF and SUMPRODUCT

[degree01]

I need to incorporate "Count IF" with the below formula. This formula looks at column A which is called "AV_GCSE_QCA". In column A it looks for any points between 52 to 55, if it falls between those figures it counts. I now need it to look at another column and see if the value is "B", if its B and between 52 to 55 than count.

=SUMPRODUCT((AV_GCSE_QCA>=52)*(AV_GCSE_QCA<55))

Please help.

 

[Robert Mika]

Your second argument should be <=55 if you want to inlcude 55 as well and the whole formula:

=SUMPRODUCT((AV_GCSE_QCA>=52)*(AV_GCSE_QCA<=55)*(B2:B30="B"))
adjust the B rage to your needs

Excel >03
=COUNTIFS(AV_GCSE_QCA,">=52",AV_GCSE_QCA,"<=55",B2:B30,"B")

 

[degree01]

It works, thanks alot.

 

[Robert Mika]

It works, thanks alot.

You are welcome.

 

[degree01]

I currently have the below formula to find out how many A grades a group of Turkish students are getting.

=SUM(COUNTIFS(Ethnicity,{"Turkish","Turkish Cypriot","Kurdish"},I2:I84,{"A"}))

Now I need to find out how many of them are getting grades As and Bs from a Column, I entered the below formula, it doesnt count B.

=SUM(COUNTIFS(Ethnicity,{"Turkish","Turkish Cypriot","Kurdish"},I2:I84,{"A","B"}))

Please help

 

[Robert Mika]

I currently have the below formula to find out how many A grades a group of Turkish students are getting.

=SUM(COUNTIFS(Ethnicity,{"Turkish","Turkish Cypriot","Kurdish"},I2:I84,{"A"}))

Now I need to find out how many of them are getting grades As and Bs from a Column, I entered the below formula, it doesnt count B.

=SUM(COUNTIFS(Ethnicity,{"Turkish","Turkish Cypriot","Kurdish"},I2:I84,{"A","B"}))

Please help

One way:

Excel 2010
	A	B	I
1	6
2
3
4
5	Turkish		A
6
7	Turkish Cypriot		A
8	Turkish		B
9
10
11	Turkish		A
12
13
14	Kurdish		b
15
16
17	Turkish Cypriot		A
Sheet1

Cell Formulas
Range	Formula
A1	=SUMPRODUCT(--(ISNUMBER(MATCH(Ethnicity,{"Turkish","Turkish Cypriot","Kurdish"},0))),--(ISNUMBER(MATCH(I2:$I$84,{"A","B"},0))))

Named Ranges
Name	Refers To	Cells
Ethnicity	=Sheet1!$A$2:A84

 

[Aladin Akyurek]

I currently have the below formula to find out how many A grades a group of Turkish students are getting.

=SUM(COUNTIFS(Ethnicity,{"Turkish","Turkish Cypriot","Kurdish"},I2:I84,{"A"}))

Now I need to find out how many of them are getting grades As and Bs from a Column, I entered the below formula, it doesnt count B.

=SUM(COUNTIFS(Ethnicity,{"Turkish","Turkish Cypriot","Kurdish"},I2:I84,{"A","B"}))

Please help

Try...

=SUM(COUNTIFS(Ethnicity,{"Turkish","Turkish Cypriot","Kurdish"},I2:I84,{"A";"B"}))

 

[degree01]

Both formulas works, thanks guys. I can sleep easy now.