Summing up with Hlookup

D

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
 
M

Max

One way
In Sheet1,
In B6:
=SUMPRODUCT((Sheet2!1:1>=B3)*(Sheet2!1:1<=B4),Sheet2!20:20)
 
M

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
 
D

Danie

Thanks Mike I will try it.

Mike H said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top