SUMIF ?! .... not quite enough....

W

WhytheQ

I have a data sheet like this:

01-Jan-07 02-Jan-07 03-Jan-07 04-Jan-07 05-Jan-07 06-Jan-07
1 1 2 1
2 8 2 2 2 8
3 3 3 3
4 4 8 6 4 4
5 5 5 3 5 6

(01-Jan-07 is in cell A1)

In another sheet I've got a cell with a date in it e.g 04-Jan-07.
How do I set up a formula that will return the sum of the numbers
below the date specified e.g if it is 04-Jan-07 then it should return
16.
please note: Is it possible to set this up without adding a row that
contains the sum of each column (as I know how to use that method)

I've played around with SUMIF without much success

Any help greatly appreciated.

Regards
Jason.
 
T

Toppers

try:

=SUMPRODUCT(--(Sheet2!A1:F1=A1)*(Sheet2!A2:F6))

A1 contains your date
B1 the above formula
Sheet2 your table

HTH
 

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