SUMPRODUCT when 3 conditions (2 text + 1 number) are fullfilled

[miss_anonymous]

Hello everyone,

this forum was highly recommanded to me.
I want to evaluate an questionnaire with Excel 2003
This is the problem i need to solved:

I need a Makro which counts all green eyed females at the age of 1.

This data is spread within 3 collumns:

Count in Column E (Text: "green", "blue", "brown") only when both additional conditions are fulfilled in the same row:
condition 1 => Column F = female (Text: female, male) and
condition 2 => Column G = 1 (Number: 1-99)

I have tried a few different formula but it is not right, yet:

=SUMPRODUCT((E1:E11="green")=IF(G1:G11=1)*(F1:F11="female")

unfortunately the countif.function does not exist for Excel.2003.

Hope you can help me!

Thank you very much,

miss anonymous

 

[Gerald Higgins]

Hi, welcome to the board.

COUNTIF does exist in 2003, but not COUNTIFS, which I think exists in later versions.

Try

=sumproduct(--(e1:e11="green"),--(g1:g11=1),--(f1:f11="female"))

 

[T. Valko]

Hello everyone,

this forum was highly recommanded to me.
I want to evaluate an questionnaire with Excel 2003
This is the problem i need to solved:

I need a Makro which counts all green eyed females at the age of 1.

This data is spread within 3 collumns:

Count in Column E (Text: "green", "blue", "brown") only when both additional conditions are fulfilled in the same row:
condition 1 => Column F = female (Text: female, male) and
condition 2 => Column G = 1 (Number: 1-99)

I have tried a few different formula but it is not right, yet:

=SUMPRODUCT((E1:E11="green")=IF(G1:G11=1)*(F1:F11="female")

unfortunately the countif.function does not exist for Excel.2003.

Hope you can help me!

Thank you very much,

miss anonymous

Use cells to hold the criteria:

• A1 = Green
• B1 = Female
• C1 = 1

Then:

=SUMPRODUCT(--(E1:E11=A1),--(F1:F11=B1),--(G1:G11=C1))

 

[miss_anonymous]

Thank you very much for your instant help

You've been most helpful, guys!

I prefer the first solution, because I do not want to change the text in coding.


Another question - refering to the same example:

I need all "green" AND "blue" coloured females at the age of 1.

Is my solution right?

=sumproduct(--(e1:e11="green" and "blue"),--(g1:g11=1),--(f1:f11="female"))


Thank you for your help!

Cheers,

miss anonymous

 

[Aladin Akyurek]

Thank you very much for your instant help

Unfortunately I do not want to change the text in coding.


Is it also possible to stick to the text for the 2. conditions (green and female), when remaining the number for the 3. condition ?

Thank you for your help!

Cheers,

miss anonymous

In that case...

=SUMPRODUCT(--(E1:E11="green"),--(G1:G11=1),--(F1:F11="female"))

 

[Aladin Akyurek]

Thank you very much for your instant help

You've been most helpful, guys!

I prefer the first solution, because I do not want to change the text in coding.


Another question - refering to the same example:

I need all "green" AND "blue" coloured females at the age of 1.

Is my solution right?

=sumproduct(--(e1:e11="green" and "blue"),--(g1:g11=1),--(f1:f11="female"))


Thank you for your help!

Cheers,

miss anonymous

Try...

=SUMPRODUCT(--ISNUMBER(MATCH(E1:E11,{"green","blue"},0)),--(G1:G11=1),--(F1:F11="female"))

 

[T. Valko]

Thank you very much for your instant help

You've been most helpful, guys!

I prefer the first solution, because I do not want to change the text in coding.

OK, then use:

=SUMPRODUCT(--(E1:E11="green"),--(F1:F11="female"),--(G1:G11=1))

Another question - refering to the same example:

I need all "green" AND "blue" coloured females at the age of 1.

Is my solution right?

=sumproduct(--(e1:e11="green" and "blue"),--(g1:g11=1),--(f1:f11="female"))


Thank you for your help!

Cheers,

miss anonymous

Try it like this...

=SUMPRODUCT(--ISNUMBER(MATCH(E1:E11,{"green","blue"},0)),--(F1:F11="female"),--(G1:G11=1))