Formula for getting data with 2 conditions

[Mandeep Dhami]

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

 

[Roger Govier]

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))

 

[Mandeep Dhami]

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

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

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