Summing cloumn data that requires conversion first.

[DanielSchwartzkopf]

AV-RequirementsOverhaul.xls
	A	B	C	D	E	F
3	Task	Manhours	Sun	Mon	Tue	Wed
4	Clean	1	X		X	X
5	Training	2		X	X	X
6	Lubricate	0.5	X	X	X
7	Repair	0.75	X		X
8	Fabricate	3		X	X	X
Sheet1

Greetings,

I have a scheduling tool I made to help my team of planners distribute work evenly over the course of a year. Right now the spreadsheet that I have developed is WAY too big (30mb). This is due mainly to the way that I am calculating daily manhour totals, which I'm sure is highly innefficient.

As a rough example, I made the table above. What I need to figure out is a dynamic way of summing manhours for any given day. Where an "X" is placed, the corresponding task in that row is scheduled for that day.

So Sunday = 2.5
Monday = 5.5
Tuesday = 7.25
Wednesday = 6

Is there a way to sum based on those criterias without writing a macro that I need to run every time data is changed? The user needs to be able to see changes on the fly. Right now all of my calculations occur without user intervention, but it takes about 5 seconds to recalculate my cumbersome, inneficient excel formulas! That adds up to a user headache sometimes! (Currently I have different sheets set up that indirectly link to the sheets with data and convert the "X"s to manhour totals)

So is there a way to do this in one cell for each column that I just can't figure out?

Thanks in advance for any help I recieve!

 

[northwolves]

=SUMIF(C2:C6,"*",$B2:$B6)

Drag it to right direction.


Best Regards

 

[Andrew Poulsom]

In C9 enter:

=SUMIF(C4:C8,"X",$B4:$B8)

and copy across.

 

[Domenic]

Let H3:H6 contain Sun, Mon, Tue, and Wed, and then try the following...

I3, copied down:

=SUMIF(INDEX($C$4:$F$8,0,MATCH(H3,$C$3:$F$3,0)),"X",$B$4:$B$8)

Note that the values in H3:H6 match the values in C3:F3.

Hope this helps!

 

[DanielSchwartzkopf]

Thank you so much!

You've made the planner/schedulers at disneyland very happy!