[Danie]

Hi, I am trying to sum up values which I obtain with an Hlookup.
On my first sheet I have a Date From in cell B3 and a Date To in cell B4. I
have to sum the values between Date From and Date To by using a Hlookup to
read data on another sheet.
Example :
Sheet 1 Cell B3 01/06/2008 (format dd/mm/yyyy)
B4 03/06/2008
B6 Total Revenue = ??? (I used Hlookup)
Sheet 2: A B C D
E (columns)
Row 1 01/06/2008 02/06/2008 03/06/2008 04/06/2008 Etc...
Row 20 2000 1500 2500 4000

In row 20 I have the Revenue. I need to add up all the figures in row 20
that falls in my date range on Sheet 1(ie here 2000+1500+2500). Can somebody
help me with this problem.
Thanks
Danielle

[Max]

One way
In Sheet1,
In B6:
=SUMPRODUCT((Sheet2!1:1=B3)*(Sheet2!1:1<=B4),Shee t2!20:20)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Danie" wrote:
Hi, I am trying to sum up values which I obtain with an Hlookup.
On my first sheet I have a Date From in cell B3 and a Date To in cell B4. I
have to sum the values between Date From and Date To by using a Hlookup to
read data on another sheet.
Example :
Sheet 1 Cell B3 01/06/2008 (format dd/mm/yyyy)
B4 03/06/2008
B6 Total Revenue = ??? (I used Hlookup)
Sheet 2: A B C D
E (columns)
Row 1 01/06/2008 02/06/2008 03/06/2008 04/06/2008 Etc...
Row 20 2000 1500 2500 4000

In row 20 I have the Revenue. I need to add up all the figures in row 20
that falls in my date range on Sheet 1(ie here 2000+1500+2500). Can somebody
help me with this problem.
Thanks
Danielle

[Mike H]

Try this in B6

=SUMIF(Sheet2!A1:Q1,"="&B3,Sheet2!A20:Q20)-SUMIF(Sheet2!A1:Q1,""&B4,Sheet2!A20:Q20)

Format as general. Adjust the ranges to suit

Mike

"Danie" wrote:

Hi, I am trying to sum up values which I obtain with an Hlookup.
On my first sheet I have a Date From in cell B3 and a Date To in cell B4. I
have to sum the values between Date From and Date To by using a Hlookup to
read data on another sheet.
Example :
Sheet 1 Cell B3 01/06/2008 (format dd/mm/yyyy)
B4 03/06/2008
B6 Total Revenue = ??? (I used Hlookup)
Sheet 2: A B C D
E (columns)
Row 1 01/06/2008 02/06/2008 03/06/2008 04/06/2008 Etc...
Row 20 2000 1500 2500 4000

In row 20 I have the Revenue. I need to add up all the figures in row 20
that falls in my date range on Sheet 1(ie here 2000+1500+2500). Can somebody
help me with this problem.
Thanks
Danielle

[Danie]

Hi Max,
It works perfectly.
Thx so much.
Danielle

"Max" wrote:

One way
In Sheet1,
In B6:
=SUMPRODUCT((Sheet2!1:1=B3)*(Sheet2!1:1<=B4),Shee t2!20:20)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Danie" wrote:
Hi, I am trying to sum up values which I obtain with an Hlookup.
On my first sheet I have a Date From in cell B3 and a Date To in cell B4. I
have to sum the values between Date From and Date To by using a Hlookup to
read data on another sheet.
Example :
Sheet 1 Cell B3 01/06/2008 (format dd/mm/yyyy)
B4 03/06/2008
B6 Total Revenue = ??? (I used Hlookup)
Sheet 2: A B C D
E (columns)
Row 1 01/06/2008 02/06/2008 03/06/2008 04/06/2008 Etc...
Row 20 2000 1500 2500 4000

In row 20 I have the Revenue. I need to add up all the figures in row 20
that falls in my date range on Sheet 1(ie here 2000+1500+2500). Can somebody
help me with this problem.
Thanks
Danielle

[Danie]

Thanks Mike I will try it.

"Mike H" wrote:

Try this in B6

=SUMIF(Sheet2!A1:Q1,"="&B3,Sheet2!A20:Q20)-SUMIF(Sheet2!A1:Q1,""&B4,Sheet2!A20:Q20)

Format as general. Adjust the ranges to suit

Mike

"Danie" wrote:

Hi, I am trying to sum up values which I obtain with an Hlookup.
On my first sheet I have a Date From in cell B3 and a Date To in cell B4. I
have to sum the values between Date From and Date To by using a Hlookup to
read data on another sheet.
Example :
Sheet 1 Cell B3 01/06/2008 (format dd/mm/yyyy)
B4 03/06/2008
B6 Total Revenue = ??? (I used Hlookup)
Sheet 2: A B C D
E (columns)
Row 1 01/06/2008 02/06/2008 03/06/2008 04/06/2008 Etc...
Row 20 2000 1500 2500 4000

In row 20 I have the Revenue. I need to add up all the figures in row 20
that falls in my date range on Sheet 1(ie here 2000+1500+2500). Can somebody
help me with this problem.
Thanks
Danielle

[Max]

Welcome, Danielle. Do spare a moment to click the "Yes" button below.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Danie" wrote:
Hi Max,
It works perfectly.
Thx so much.
Danielle