# Sumif and Colorfunction Colaboration.

#### HVAC Esti

##### New Member
I am looking for a way to add the colorfunction and sumif options together. What I have are multiple sheets in an excel file the "main" tab has a list of pieces of equipment for a project we are bidding (sometimes up to 1000 lines) on this tab we use colors to differentiate which division of our business will be installing the piece of equipment and at the bottom of this tab are cells totaling the hours for each division per the assigned color (using colorfunction, so the colorfuntion VB works). On another tab "units" we have breakouts that are determined by who we are sending our bid to (this tab changes per project and has had up to 80 rows for breakout). On the "units" tab there is a list of how the owner wants the project broken down and we number that list to make it easier to keep track of. For costs I can use the sumif function and get the correct price for the equipment into the correct breakout row. On this tab we have 7 columns for each division. What I would like to do is something like this =sumif(column on main tab with row numbers in it, colorfunction(color of division,columns were hrs are, True),columns were hrs are) I know that I can't combine the VB function with the sumif though.

I'm not sure if my above explanation makes sense, hopefully it does. I hadn't seen any threads like this yet other than the countif ones. If there is already a thread addressing this please send me there. I'll try to get a file together that I can share that doesn't have "company secrets" in it or that if i shared would cause me to get fired and get it on here.

#### Yongle

##### Well-known Member
What is the =IF(SUM(\$L5:\$M5)=0,"",IFERROR(LOOKUP(999,A\$5:A5),"")) formula for?

did you try it?
- test it on the file you dropboxed
- it shows you why it is safer to put a value in column A rather than what you are currently doing

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### HVAC Esti

##### New Member
did you try it?
- test it on the file you dropboxed
- it shows you why it is safer to put a value in column A rather than what you are currently doing

It just puts the number from column A into column N if there is something in column L:M ... am I missing something you said above?

#### Yongle

##### Well-known Member
1
It just puts the number from column A into column N if there is something in column L:M ... am I missing something you said above?
Only if you did not help you spot the inconsistency in "Main" in dropboxed workbook
- you will not be using that formula
- it was provided to help explain why a value is required in EVERY row in column A (which you have already accepted)

2
I am prepared to put a "row number" in every cell in column A if that is what is needed
good - that's great
- it makes it easier to create a reliable function for you
- I will post it tomorrow after testing etc

3
In the meantime, for you to ponder ...
Is there a logical reason for hanging on to the current layout ?
- "the tail" is currently "wagging the dog"
- using colours for a bit of extra info can be useful
- but superimposing colours after the fact and using the UDF to drive key basic data is rather limiting in the longer term
- it is also making workbook recalculation much slower
- there are a lot of tools in Excel that you are currently unable to use because of the layout of your data in "Main"
- it is also why you another UDF is required for a basic bit of analysis
- the future will be no different unless you change the layout

#### Yongle

##### Well-known Member
I already have this working as a macro, next step is creating a robust UDF that can be used as a formula

#### HVAC Esti

##### New Member

The layout of "main" is due to a company standard, not something I can change. We try to keep the width to what it is so that it can be printed on an 8.5x11 landscape and be easily read.
I realize that some of the columns aren't titled at the top.

A better layout may be possible it just means change for a lot of people in our company and, you know how people are with change. Not being the boss it may be hard to get a change.

The =IF(SUM(\$L5:\$M5)=0,"",IFERROR(LOOKUP(999,A\$5:A5),"")) formula does cause it to run a lot slower.

I'm not sure what excel tools we could use to help us with this out of the ones that are unable to be used. I don't really know what all is available.

I don't know what you mean by "it is also why you another UDF is required for a basic bit of analysis "

I already have this working as a macro, next step is creating a robust UDF that can be used as a formula
That is Awesome!

#### Yongle

##### Well-known Member
The layout of "main" is due to a company standard, not something I can change. We try to keep the width to what it is so that it can be printed on an 8.5x11 landscape and be easily read.
I realize that some of the columns aren't titled at the top.
A better layout may be possible it just means change for a lot of people in our company and, you know how people are with change. Not being the boss it may be hard to get a change.
that is what I suspected

I'm not sure what excel tools we could use to help us with this out of the ones that are unable to be used. I don't really know what all is available.
I don't know what you mean by "it is also why you another UDF is required for a basic bit of analysis "
Excel can do everything you require very simply without any extra functions
- the current corporate approach is making life harder than it needs to be
- I will provide example different layouts etc AFTER we have resolved your problem

#### HVAC Esti

##### New Member

Yongle,

I haven't heard anything in a while. Is everything ok?

Thanks,

#### Yongle

##### Well-known Member
will have some time tomorrow

#### HVAC Esti

##### New Member
Great!

I really appreciate it.

#### HVAC Esti

##### New Member
Yongle,

I haven't seen a reply from you in a while and I've been busy at work. How are things coming with this?

Replies
8
Views
201
Replies
27
Views
1K
Replies
11
Views
202
Replies
0
Views
204
Replies
5
Views
186

1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

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