Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
SUMIF | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |