Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Totaled based on Matching Identical Criteria in Worksheet
Hello,
First, thanks for taking a look. I have a spreadsheet that has dates in Column A beginning on Row 3. The date is lised in this format: Nov-08 In Row 1, beginning in Column B, our several other dates all listed by month beginning with this month and ending 36 months in the past (goes back 1 month in each cell). These dates are all listed in the same format (i.e. Apr-09). Cell B1 has Apr-09 while cell C1 has Mar-09, etc. In row 3 - beginning in Column B I will have numbers (dollar figures) below each date in Row 1. I need to match the date in Column A, row 3 (example: Nov-08) to the same date in Row 1 (in this example it would end up being G1 (Nov-08). I need a formula that tells my sum to only include data to the left of Nov-08 in row 1 because it matches the data in A3. There will be data entered in Columns H - Z but I only want my sum to include those amounts in Columns B-H (based on the matching dates). Column A Column D Column E Column F Column G Column H Feb-09 Jan-09 Dec-08 Nov-08 Oct-08 Nov-08 $5 $10 $7 $5 $15 The sum I am hoping to get (in AA3) to be $27 (Columns D - G -- Not H). In row 4 I will have a different date (like Jan-09) and new dollar amounts. The formula will adjust the sum based on the matching dates. THANK YOU FOR ANY HELP YOU CAN PROVIDE!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Totaled based on Matching Identical Criteria in Worksheet
The date is lised in this format: Nov-08
Ok, so what is the actual date? Assuming the dates are the 1st of the month. For example... G1 real date is 11/1/2008 formatted to display as Nov-08 A3 real date is 11/1/2208 formatted to display as Nov-08 Try this: =SUMIF(B1:K1,""&A3,B3:K3) -- Biff Microsoft Excel MVP "Rob" wrote in message ... Hello, First, thanks for taking a look. I have a spreadsheet that has dates in Column A beginning on Row 3. The date is lised in this format: Nov-08 In Row 1, beginning in Column B, our several other dates all listed by month beginning with this month and ending 36 months in the past (goes back 1 month in each cell). These dates are all listed in the same format (i.e. Apr-09). Cell B1 has Apr-09 while cell C1 has Mar-09, etc. In row 3 - beginning in Column B I will have numbers (dollar figures) below each date in Row 1. I need to match the date in Column A, row 3 (example: Nov-08) to the same date in Row 1 (in this example it would end up being G1 (Nov-08). I need a formula that tells my sum to only include data to the left of Nov-08 in row 1 because it matches the data in A3. There will be data entered in Columns H - Z but I only want my sum to include those amounts in Columns B-H (based on the matching dates). Column A Column D Column E Column F Column G Column H Feb-09 Jan-09 Dec-08 Nov-08 Oct-08 Nov-08 $5 $10 $7 $5 $15 The sum I am hoping to get (in AA3) to be $27 (Columns D - G -- Not H). In row 4 I will have a different date (like Jan-09) and new dollar amounts. The formula will adjust the sum based on the matching dates. THANK YOU FOR ANY HELP YOU CAN PROVIDE!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching identical data using data only once in the matching proce | Excel Discussion (Misc queries) | |||
Copy Row to worksheet based on criteria | Excel Discussion (Misc queries) | |||
Transferring data from one worksheet to another based on criteria | Excel Discussion (Misc queries) | |||
Copying data from one worksheet to another based on criteria | Excel Discussion (Misc queries) | |||
Copy all records matching "Text" criteria to new worksheet | Excel Worksheet Functions |