Ask for help for multiple criteria

[Emily]

Dear All,

For the following data, how can I obtain the frequency if row 1 is C and row 2 is 1990.

A B C C C B C A C
1992 1990 1990 1923 1990 2050 1901 1990 1991

 

[Aladin Akyurek]

=SUMPRODUCT((A1:I1="C")*(A2:I2=1990))

 

[Emily]

Thanks.
I never thought using "SUMPRODUCT". Is it possible to use COUNTIF instead?

 

[Aladin Akyurek]

On 2002-09-21 20:56, Emily wrote:
Thanks.
I never thought using "SUMPRODUCT". Is it possible to use COUNTIF instead?

No. See http://www.mrexcel.com/wwwboard/messages/8961.html

You could use DCOUNT, but if you intend counting for multiple sets of conditions, this database function is not hassle-free.

 

[Yogi Anand]

Aladin's formula is elegant and efficient. Just to add some alternates that are not so efficient in this case ...

{=SUM(IF((A1:I1="c")*(A2:I2=1990),1,0))}

or

{=SUM(IF(A1:I1="C",IF(A2:I2=1990,1,0),0))}

Regards!

Yogi

 

[Emily]

Thanks a lot