Summing distinct columns

[SFO User]

I'm trying to sum TASK by hours over a 52 week period - ie. task A has X
number of hours associated with it over the illustrated 52 week period.

I have 3 columns of data per weekly period (NAME(s), TASK(s), HOURS). A
person can log hours on multiple tasks each week.


NAME, TASK, HOURS (this info is repeated 52 times across the columns)
Bill | task A | 25 |
Joe, task B, 22 |
Jill, task C, 33 |
Bill, task C, 20 |

Can I use some version of a SUMIF formula here?

Cheers. SFO User

 

[Marcelo]

Sumproduct could be help you

on C2
=sumproduct(--(Sheet2!A2:A1000=a2)*(Sheet2!b2:b1000=b2),(Sheet2!c2:c1000))

Assuming:
your data is in sheet2
A2 has the name
b2 has the task
c2 has the function that will return to the sum rescpection the two
conditions


hth

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



"SFO User" escreveu:

 

[SFO User]

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!

Cheers.

 

[Spiky]

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.

 

[SFO User]

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

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.

 

[Spiky]

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

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.