Formula for getting data with 2 conditions
 

Hi,

I have somedata in one excel file (say Data1.xls) and want the summary
(Count) of that data into another excel file (say Result1.xls).

There should be 2 conditions to it. The first one is I have got a date in
Result1.xls (say 1-Jan-08) this date should be checked in Data1.xls and if it
matches then the second condition is Name in Result1.xls (say John). If date
and name is 1-Jan-08 and John respectively then I should get the Count of
name (John) on 1-Jan-08 in Result1.xls.

I tried with Countif function, but I am getting any one of the above
condition and not both.

Hope I am able to convey what I require.

Cheers,
Mandeep Dhami

Formula for getting data with 2 conditions
 

Hi

Assuming Date is in column A and Name in column B
=SUMPRODUCT(([Data1.xls]Sheet1!$A$2:$A$1000=--"01 Jan
08")*([Data1.xls]Sheet1!$B$2:$B$1000="John"))

Change references and sheet names as appropriate.

Better still would be to put the Date and Name in cells on Result Sheet, say
in A1 Date required and B1 Name required then use
=SUMPRODUCT(([Data1.xls]Sheet1!$A$2:$A$1000=A1)*([Data1.xls]Sheet1!$B$2:$B$1000=B1))

--

Regards
Roger Govier

"Mandeep Dhami" wrote in message
...
Hi,

I have somedata in one excel file (say Data1.xls) and want the summary
(Count) of that data into another excel file (say Result1.xls).

There should be 2 conditions to it. The first one is I have got a date in
Result1.xls (say 1-Jan-08) this date should be checked in Data1.xls and if
it
matches then the second condition is Name in Result1.xls (say John). If
date
and name is 1-Jan-08 and John respectively then I should get the Count of
name (John) on 1-Jan-08 in Result1.xls.

I tried with Countif function, but I am getting any one of the above
condition and not both.

Hope I am able to convey what I require.

Cheers,
Mandeep Dhami

Formula for getting data with 2 conditions
 

Thanks a lot Roger......your solution worked.

"Roger Govier" wrote:

Hi

Assuming Date is in column A and Name in column B
=SUMPRODUCT(([Data1.xls]Sheet1!$A$2:$A$1000=--"01 Jan
08")*([Data1.xls]Sheet1!$B$2:$B$1000="John"))

Change references and sheet names as appropriate.

Better still would be to put the Date and Name in cells on Result Sheet, say
in A1 Date required and B1 Name required then use
=SUMPRODUCT(([Data1.xls]Sheet1!$A$2:$A$1000=A1)*([Data1.xls]Sheet1!$B$2:$B$1000=B1))

--

Regards
Roger Govier

"Mandeep Dhami" wrote in message
...
Hi,

I have somedata in one excel file (say Data1.xls) and want the summary
(Count) of that data into another excel file (say Result1.xls).

There should be 2 conditions to it. The first one is I have got a date in
Result1.xls (say 1-Jan-08) this date should be checked in Data1.xls and if
it
matches then the second condition is Name in Result1.xls (say John). If
date
and name is 1-Jan-08 and John respectively then I should get the Count of
name (John) on 1-Jan-08 in Result1.xls.

I tried with Countif function, but I am getting any one of the above
condition and not both.

Hope I am able to convey what I require.

Cheers,
Mandeep Dhami