Return a value based on 2 or more columns of information
 

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:D<=C)),FALSE) and
=IF(AND(C:C=F,D: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.
--
Kind regards,
Becca

Return a value based on 2 or more columns of information
 

Becca.T. wrote:
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:D<=C)),FALSE) and
=IF(AND(C:C=F,D: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 he

http://www.contextures.com/xlFunctio...tml#SumProduct

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

Return a value based on 2 or more columns of information
 

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

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

Luke M
*Remember to click "yes" if this post helped you!*


"Becca.T." wrote:

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:D<=C)),FALSE) and
=IF(AND(C:C=F,D: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.
--
Kind regards,
Becca