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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

northwolves

Well-known Member
Joined
Jun 21, 2006
Messages
1,128
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
  6. 2007
  7. 2003 or older
Platform
  1. Windows
=SUMIF(C2:C6,"*",$B2:$B6)

Drag it to right direction.


Best Regards
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,802
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!
 

Forum statistics

Threads
1,186,156
Messages
5,956,254
Members
438,243
Latest member
FXA

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