Return a value based on 2 or more columns of information

[Becca.T.]

Hello All

I was wondering if anyone out there could help me, my quest for a formula is
probably very easy to solve, but I'm just hitting a blank everytime.
Please see the table below:-
ID Student Gender English
1 A Student F B
2 B Student M D
3 C Student M F
4 D Student M G
5 E Student F A
6 F Student F F
7 G Student M B
8 H Student M C
9 I Student F D
10 J Student M C
11 K Student F C
12 L Student F F

Basically what I want is a formula that will answer this; how many females
have achieved A-C grades.
I've tried =IF(C:C=F,(COUNTIF(D<=C)),FALSE) and
=IF(AND(C:C=F,D<=C),(COUNTA(A:A)),"CHECK"). I've tried other variations,
but I just keep getting an error message and no answer - I think I'm going
mad - does anyone have any suggestions for me?

Thank you very much in advance.

 

[Glenn]

Hello All

I was wondering if anyone out there could help me, my quest for a formula is
probably very easy to solve, but I'm just hitting a blank everytime.
Please see the table below:-
ID Student Gender English
1 A Student F B
2 B Student M D
3 C Student M F
4 D Student M G
5 E Student F A
6 F Student F F
7 G Student M B
8 H Student M C
9 I Student F D
10 J Student M C
11 K Student F C
12 L Student F F

Basically what I want is a formula that will answer this; how many females
have achieved A-C grades.
I've tried =IF(C:C=F,(COUNTIF(D<=C)),FALSE) and
=IF(AND(C:C=F,D<=C),(COUNTA(A:A)),"CHECK"). I've tried other variations,
but I just keep getting an error message and no answer - I think I'm going
mad - does anyone have any suggestions for me?

Thank you very much in advance.

Look here:

http://www.contextures.com/xlFunctions01.html#SumProduct

Keep in mind that you should be referencing "F" (and "C")in your formulas.

 

[Luke M]

Try this:
=SUMPRODUCT(--(C2:C100="F"),--ISNUMBER(SEARCH(D2100,"ABC")))

Adjust range sizes as needed. Note that you can't callout entire columns
(C:C) unless using XL 2007.