SUMPRODUCT

[Aditya]

Hello.
I am trying to calculate minutes from a conference
database record within a given period.

This is what my sheet looks like.

Date Time Country Minutes Country Minutes

20 June 4:45 AM USA 29 USA 110
23 June 3:20 PM USA 12
12 July 12:02 AM CAN 42
14 Aug 4:40 PM AUS 23
16 Aug 2:30 PM USA 69


Right now i am using SUMPRODUCT as below:

=SUMPRODUCT(('E:\CONFERENCE\CONFERENCE\[A B RAMI.xls]
Sheet1'!$C$5:$C$200=$E$2)*('E:\CONFERENCE\CONFERENCE\[A B
RAMI.xls]Sheet1'!$D$5:$D$200))

This gives me total minutes to USA and works fine.
I want to have another two cells "starting Date"
and "Ending Date". Meaning i want to calculate minutes to
USA from 20 July to 25 July ie : 41 Minutes.

Like i have kept E2 for entering a country i can keep E3
and E4 for entering dates, and i should get the minutes in
the above cell. If no date is entered it should show me
Total minutes without any date range. Is this possible?

Thanks.

 

[Biff]

Hi!

Try this:

=IF(AND(E3="",E4=""),SUMPRODUCT(--(C2:C6=E2),D26),
SUMPRODUCT(--(A2:A6>=E3),--(A2:A6<=E4),--(C2:C6=E2),D26))

You need to add the path and adjust the ranges to suit.

Also, based on your sample data:

i want to calculate minutes to USA from 20 July to 25
July ie : 41 Minutes.

Did you mean June?

Biff

-----Original Message-----
Hello.
I am trying to calculate minutes from a conference
database record within a given period.

This is what my sheet looks like.

Date Time Country Minutes Country Minutes

20 June 4:45 AM USA 29 USA 110
23 June 3:20 PM USA 12
12 July 12:02 AM CAN 42
14 Aug 4:40 PM AUS 23
16 Aug 2:30 PM USA 69


Right now i am using SUMPRODUCT as below:

=SUMPRODUCT(('E:\CONFERENCE\CONFERENCE\[A B RAMI.xls]
Sheet1'!$C$5:$C$200=$E$2)*('E:\CONFERENCE\CONFERENCE\[A B
RAMI.xls]Sheet1'!$D$5:$D$200))

This gives me total minutes to USA and works fine.
I want to have another two cells "starting Date"
and "Ending Date". Meaning i want to calculate minutes to
USA from 20 July to 25 July ie : 41 Minutes.

Like i have kept E2 for entering a country i can keep E3
and E4 for entering dates, and i should get the minutes in
the above cell. If no date is entered it should show me
Total minutes without any date range. Is this possible?

Thanks.
.