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

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.

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

try:

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

A1 contains your date
B1 the above formula
Sheet2 your table

HTH

"WhytheQ" wrote:

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.

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

or simply ..
=SUMPRODUCT((Sheet2!A1:F1=A1)*(Sheet2!A2:F6))


"Toppers" wrote:

try:

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

A1 contains your date
B1 the above formula
Sheet2 your table

HTH

"WhytheQ" wrote:

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.

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

=SUM(INDEX(Sheet1!A2:F6,0,MATCH(Sheet1!A9,Sheet1!A 1:F1,0)))


"WhytheQ" wrote:

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.

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

Thanks very much!!


On 4 Jul, 14:38, Teethless mama
wrote:
=SUM(INDEX(Sheet1!A2:F6,0,MATCH(Sheet1!A9,Sheet1!A 1:F1,0)))



"WhytheQ" wrote:
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.- Hide quoted text -

- Show quoted text -