Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find last column matching criteria
Hello, I have a spreadsheet tracking actual and projected costs. I need to
sum the rows where the header indicates "Actual" and, separately, where the header indicates "Projected". Example: Cells B5:H5 contain the text "Actual" Cells I5:M5 contain the text "Projected" Cells B6:M6 contains a date showing the month & year. Cells B7 through M7 contain numeric data. Each month data is added to the next column and that columns Row 5 is changed from "Projected" to "Actual". I need Column N to SUM row 8 for all columns that contain "Actual" in Row 5 and Column O to sum row 8 for all columns that contain "Projected" in row 5. This is a simple example of a large spreadsheet. It actually contains several years of data that I will need to create these SUM columns for each fiscal year. I am thinking if I can identify the last column within that fiscal year that shows "Actual" in row 5 I can create the SUM formula based on the column numbers. Thanks for any help you can provided. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find last column matching criteria
Check out the wonderful function SUMIF!
Actuals: =SUMIF(B5:M5,"Actual",B8:M8) Projected: =SUMIF(B5:M5,"Projected",B8:M8) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "KCK" wrote: Hello, I have a spreadsheet tracking actual and projected costs. I need to sum the rows where the header indicates "Actual" and, separately, where the header indicates "Projected". Example: Cells B5:H5 contain the text "Actual" Cells I5:M5 contain the text "Projected" Cells B6:M6 contains a date showing the month & year. Cells B7 through M7 contain numeric data. Each month data is added to the next column and that columns Row 5 is changed from "Projected" to "Actual". I need Column N to SUM row 8 for all columns that contain "Actual" in Row 5 and Column O to sum row 8 for all columns that contain "Projected" in row 5. This is a simple example of a large spreadsheet. It actually contains several years of data that I will need to create these SUM columns for each fiscal year. I am thinking if I can identify the last column within that fiscal year that shows "Actual" in row 5 I can create the SUM formula based on the column numbers. Thanks for any help you can provided. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find last column matching criteria
Thanks, I completely forgot about the SUMIF function. It works perfectly when
I do not take into account the fiscal year date ranges. I tried using it in conjunction with an OFFSET formula to locate just those specific columns for the date range of the fiscal year but without success. =SUM(OFFSET($D8:$O8,,(COLUMNS($CA8:CC8)-1)*12)) This formula locates the range of columns for each fiscal year. It works fine for finding the requred columns and summing them. However, I need to modify (or use something else) to narrow the results down to those columns with "ACTUAL" in row 5.I tried to modify it as follows but it simply sums all the columns with "ACTUAL" in row 5. =SUMIF($B5:$BW5,"ACTUAL",(OFFSET($B8:$M8,,(COLUMNS ($CB8:CC8)-1)*12))) "Luke M" wrote: Check out the wonderful function SUMIF! Actuals: =SUMIF(B5:M5,"Actual",B8:M8) Projected: =SUMIF(B5:M5,"Projected",B8:M8) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "KCK" wrote: Hello, I have a spreadsheet tracking actual and projected costs. I need to sum the rows where the header indicates "Actual" and, separately, where the header indicates "Projected". Example: Cells B5:H5 contain the text "Actual" Cells I5:M5 contain the text "Projected" Cells B6:M6 contains a date showing the month & year. Cells B7 through M7 contain numeric data. Each month data is added to the next column and that columns Row 5 is changed from "Projected" to "Actual". I need Column N to SUM row 8 for all columns that contain "Actual" in Row 5 and Column O to sum row 8 for all columns that contain "Projected" in row 5. This is a simple example of a large spreadsheet. It actually contains several years of data that I will need to create these SUM columns for each fiscal year. I am thinking if I can identify the last column within that fiscal year that shows "Actual" in row 5 I can create the SUM formula based on the column numbers. Thanks for any help you can provided. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find matching records in two tables using mulitiple criteria | Excel Worksheet Functions | |||
Matching column criteria in a one to many relationship | Excel Worksheet Functions | |||
Need to find matching criteria in 1 column, then add amounts in a | Excel Worksheet Functions | |||
How can find a value using two different matching criteria? | Excel Worksheet Functions | |||
Find largest alphanumeric value matching alpha criteria in databas | Excel Worksheet Functions |