Sum with conditions

[Manosh]

Hi all,
I'd like to sum a column that contains a mix of numbers and text based on a condition that appears in a cell that is 2 rows 'down' from the number.
The structure looks something like this:
Week 1 Week 2...
500
Home
Name1 1
1000
Home
Name2 2
500
Home
Name1 1

In this case if i want to sum just for
Name1 i should get 1000 (500+500) for Week 1, and
Name2 i should get 1000 (1000) for Week 1.
I need to do this over several weeks and i may have more that 2 names.
Is there a way that you can recommend i achieve this?
Unfortunately i cannot change the structure!
Many thanks in advance!
Manosh

 

[prestonplace]

Hi all,
I'd like to sum a column that contains a mix of numbers and text based ona condition that appears in a cell that is 2 rows 'down' from the number.
The structure looks something like this:
          Week 1    Week 2...
          500
          Home
Name1     1
          1000
          Home
Name2     2
          500
          Home
Name1     1

In this case if i want to sum just for
Name1 i should get 1000 (500+500) for Week 1, and
Name2 i should get 1000 (1000) for Week 1.
I need to do this over several weeks and i may have more that 2 names.
Is there a way that you can recommend i achieve this?
Unfortunately i cannot change the structure!
Many thanks in advance!
Manosh

Manosh,

I believe the SUMIF command will meet your needs. In your example,
let's say Name 1 is in cell A4 and Week 1 is cell B1. Your
calculation cell should read:

=SUMIF(A4:A1000,"Name 1",B2:B998)

Note that the ranges in the formula are offset by 2 - this is how it
picks up the value 2 above the records that match the SUMIF.

I hard-coded "Name 1" into the formula, but obviously this could be
set to reference a cell that had the name you wanted to query on.

Hopefully this helps.
Charles