Summing Multiple Columns

[SFO User]

This is really difficult trying to explain a problem without sending an
actual spreadsheet. Here is the data in a more concise format...

Week 1 Week 2
Name Task Hours Name Task Hours
Bill a 10 Bill b 15
Bill b 20 Joe d 15
Sue a 30 Sue a 5
Bill c 10 Bill c 5

I want to get the total number of hours spent on Task "a" across all weeks.
In this example the answer would be 45 (week 1: Bill 10 + Sue 30 + week 2:
Sue 5). This table will have 52 weeks in it.

Any help would be appreciated.

SFO User

 

[Marcelo]

Assuming

Week1
Name = Column A
Task = Column B
Hours = Column C

Week 2
Name = Column D
Task = Column E
Hours = Column F

=sumproduct(--(b2:b100="a"),(c2:c100))+sumproduct(--(e2:e100="a"),(f2:f100))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"SFO User" escreveu:

 

[SFO User]

Marcelo,

If I have 52 weeks across 150 columns of data... with this formula, wouldn't
the fomula become enormous? I'm trying to avoid creating a monster formula.

Thoughts?

 

[Bob Phillips]

=SUM(IF(((MOD(COLUMN(B2:EX20),3)=2)*(B2:EX20="a")),C2:EY20))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note how the colums are offset.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[SFO User]

Bob... you are a genius! I've been looking for this type of a solution for a
long time.

Thanks for your time and effort on this!

 

[SFO User]

Bob,

When I insert a column in front the data table it disrupts the formula
listed below and gives me a value of 0. Not fully understanding the formula
yet, what do I need to change in order for it to work again. (when I removed
the additional column, the formula worked again).

Cheers. SFO User

 

[Bob Phillips]

=SUM(IF(((MOD(COLUMN(B2:EX20)-COLUMN(B2),3)=0)*(B2:EX20="a")),C2:EY20))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[SFO User]

Once again, works like a charm!

Thank you.

SFO User