Summing cloumn data that requires conversion first.

DanielSchwartzkopf

New Member
Joined
Apr 26, 2007
Messages
16
AV-RequirementsOverhaul.xls
ABCDEF
3TaskManhoursSunMonTueWed
4Clean1XXX
5Training2XXX
6Lubricate0.5XXX
7Repair0.75XX
8Fabricate3XXX
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!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,936
Office Version
  1. 365
Platform
  1. Windows
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,159
Messages
5,768,523
Members
425,479
Latest member
Neerajcool

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top