# Summing cloumn data that requires conversion first.

#### DanielSchwartzkopf

##### New Member
AV-RequirementsOverhaul.xls
ABCDEF
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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
=SUMIF(C2:C6,"*",\$B2:\$B6)

Drag it to right direction.

Best Regards

In C9 enter:

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

and copy across.

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!

Thank you so much!

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

Replies
7
Views
929
Replies
18
Views
779
Replies
1
Views
2K
Replies
0
Views
1K
Replies
2
Views
1K

1,220,986
Messages
6,157,229
Members
451,406
Latest member
Kevin_267

### 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.

### Which adblocker are you using?

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

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