Sum a partial column?

Question

My apologies to all of you at Microsoft Answers.  I posted a question earlier and was marvelously helped by Lars-Åke Aspelin.  I regret that it took that Q and A with Lars for me to realize that I was not compiling the correct numbers.  Lars is an amazingly talented person, but I am embarrassed to ask him for help again under the circumstances.  Thanks again Lars.  These are the facts as they should have appeared in my previous question:

I need a formula that returns the following:

The sum of each of the values in column W in "DataSheet" which is on the same row as an E that matches the C of the target row for the formula...over the bottommost 225 rows of "DataSheet"

The sum of each of the values in column W in "DataSheet" which is on the same row as an E that matches the C of the target row for the formula...over the bottommost  120 rows of "DataSheet"

Some facts about "DataSheet".

1.  The data field begins on row 18 and ends on row 962.
2.  The top 225 rows are always populated.  These rows are 18:242
3.  At the beginning of the new year, data is added each week to "DataSheet" beginning on row 243 for the first week
4.  Each data entry cycle consists of 15 rows of data.
5.  There will never be more than 962 rows of data total, including the top 225 rows of preexisting data.

I would be absolutely grateful to anyone who can break down these two queries and provide me with the correct formulas...thankyou!

Answer 1

My apologies to all of you at Microsoft Answers.  I posted a question earlier and was marvelously helped by Lars-Åke Aspelin.  I regret that it took that Q and A with Lars for me to realize that I was not compiling the correct numbers.  Lars is an amazingly talented person, but I am embarrassed to ask him for help again under the circumstances.  Thanks again Lars.  These are the facts as they should have appeared in my previous question:

I need a formula that returns the following:

The sum of each of the values in column W in "DataSheet" which is on the same row as an E that matches the C of the target row for the formula...over the bottommost 225 rows of "DataSheet"

The sum of each of the values in column W in "DataSheet" which is on the same row as an E that matches the C of the target row for the formula...over the bottommost  120 rows of "DataSheet"

Some facts about "DataSheet".

1.  The data field begins on row 18 and ends on row 962.
2.  The top 225 rows are always populated.  These rows are 18:242
3.  At the beginning of the new year, data is added each week to "DataSheet" beginning on row 243 for the first week
4.  Each data entry cycle consists of 15 rows of data.
5.  There will never be more than 962 rows of data total, including the top 225 rows of preexisting data.

I would be absolutely grateful to anyone who can break down these two queries and provide me with the correct formulas...thankyou!

Try these two array formulas:

=SUMIFS(OFFSET(DataSheet!W$1,MAX(IF(DataSheet!E$242:E$962<>"",ROW($242:$962)-225,0)),,225),

                OFFSET(DataSheet!E$1,MAX(IF(DataSheet!E$242:E$962<>"",ROW($242:$962)-225,0)),,225),C2)

=SUMIFS(OFFSET(DataSheet!W$1,MAX(IF(DataSheet!E$242:E$962<>"",ROW($242:$962)-120,0)),,120),

                OFFSET(DataSheet!E$1,MAX(IF(DataSheet!E$242:E$962<>"",ROW($242:$962)-120,0)),,120),C2)

Explanation:

The part

OFFSET(DataSheet!W$1,MAX(IF(DataSheet!E$242:E$962<>"",ROW($242:$962)-225,0)),,225)

defines the range in the W column with the 225 last entries.

This is the range to be conditionally summed. The condition is defined on a similar range in the E column where the value is equal to the value in cell C2.

Hope this helps / Lars-Åke

EDIT: Slightly shorter versions of the formulas

=SUMIFS(OFFSET(DataSheet!W$1,MAX(IF(DataSheet!E$242:E$962<>"",ROW($241:$961))),,-225),

          OFFSET(DataSheet!E$1,MAX(IF(DataSheet!E$242:E$962<>"",ROW($241:$961))),,-225),C2)

=SUMIFS(OFFSET(DataSheet!W$1,MAX(IF(DataSheet!E$242:E$962<>"",ROW($241:$961))),,-120),

                OFFSET(DataSheet!E$1,MAX(IF(DataSheet!E$242:E$962<>"",ROW($241:$961))),,-120),C2)

MORE EDIT:

As you have hundreds of rows in your sheet with these array formulas, you may get a slow response as there are a lot of computation to do. It may also be easier to understand the formula if you do like this instead.

Find four spare cells anywhere in the sheet where you put the following formulas. I used cells AF1:AI1

Note: All four formulas are array formulas. Enter with CTRL+SHIFT+ENTER:

In cell AF1:

="DataSheet!W"&MAX(IF(DataSheet!E$242:E$962<>"",ROW($243:$963)))-225&":W"&MAX(IF(DataSheet!E$242:E$962<>"",ROW($242:$962)))

In cell AG1:

="DataSheet!E"&MAX(IF(DataSheet!E$242:E$962<>"",ROW($243:$963)))-225&":E"&MAX(IF(DataSheet!E$242:E$962<>"",ROW($242:$962)))

In cell  AH1:

="DataSheet!W"&MAX(IF(DataSheet!E$242:E$962<>"",ROW($243:$963)))-120&":W"&MAX(IF(DataSheet!E$242:E$962<>"",ROW($242:$962)))

In cell AI1:

="DataSheet!E"&MAX(IF(DataSheet!E$242:E$962<>"",ROW($243:$963)))-120&":E"&MAX(IF(DataSheet!E$242:E$962<>"",ROW($242:$962)))

These cells will now display the ranges that are used in the, now much shorter, SUMIFS formulas:

Note: The SUMIFS can now be entered as ordinary formulas, just ENTER

=SUMIFS(INDIRECT($AF$1),INDIRECT($AG$1),C2)

=SUMIFS(INDIRECT($AH$1),INDIRECT($AI$1),C2)

EDIT: You should be aware of that the formulas in cells AF1:AI1 do not correctly adjust themselves if you make changes to your DataSheet worksheet like inserting och deleting columns or renaming the sheet.

They are thus a bit "risky" to use, so if you can live with the calculation time I would recommend the SUMIFS formulas including the OFFSET.

Hope this helps / Lars-Åke

Answer 2

More brilliant work, Lars.

I have finished the body of my spreadsheet with your array formulas from this post.

I was trying to avoid you for this question.  I realized through the course your tutorial in my previous thread on this topic that my query for Y and Z was not what I needed.  I thought I was being clever adding those columns, but now I am left to consider what might be a use for them.

I may look into a chart using the numbers at some point.

This has been a very instructional project for me.  I have learned much about the advances in spreadsheets through the effort.  The final spreadsheet will help me be able to spend a little less time on golf, too, which is a plus for me.  I am hoping to use it to track the players for a Fantasy Golf game at Yahoo! that I enjoy but that was quite burdensome for me before.  I believe this will help.

I contacted a moderator at  a golf forum of which I am a member yesterday to see how he likes the file.  It could end up with the forum administrators as a regular download for users and friends of the board.  If so, I hope many golf fans get to take advantage of your work.  Thanks.

I was considering your idea with the "array warp" as I will call it.  Very clever.  I am not adventurous enough to try it, however.  I know it would work, but I am satisfied with the load times, even though they are long....

Many happy returns and a joyous holiday season to you and to all the great counselors at Microsoft Answers!