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.

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.

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.