On May 29, 12:24 pm, SFO User
wrote:
Spiky,

This is really difficult trying to explain a problem without sending an
actual spreadsheet. I tried your suggestion and got a #VALUE! error. 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.

If the first "Bill" entry in the table is cell A6, I tried the following
formula changing the C1000 with R1000 (with R being the last column in the
table):

=SUMPRODUCT(--(Sheet1!A6:A1000=A2)*(Sheet1!B6:B1000=B2),(Sheet1! C6:R1000))

Let me know if I need to try something completely different.

Thanks in advance!

SFO User

"Spiky" wrote:
On May 28, 11:26 pm, SFO User
wrote:
Marcelo...

I'm trying to sum weekly data across columns:

Week 1 Week 2
Week 3
A2 [Name], B2 [Task], C2 [Hours], D2 [Name], E2 [Task], F2 [Hours],.... etc

There can be 6 different task types.

I just want to sum the number of hours for each task type across all of the
weeks.

Any help would be appreciated!

Use the formula above, but change the C in "C1000" to the last column
of your data.

It seems that you have too many columns for the formulas above. Some
of them are text, which are causing the VALUE error. I missed that
when making my last suggestion. I'm not sure how to make SUMPRODUCT
work over non-adjacent columns. It may be possible with some OFFSET or
other additions to the formula.

Does the table need to be in this format? It would be much easier to
make formulas to analyze the data if you simply had a 4 column setup:
Name, Week, Task, Hours. This would make your data much more vertical,
which is what Excel likes. Then the above SUMPRODUCT formulas could
easily be tweaked to give the answers you need.