Sumif and Colorfunction Colaboration.

HVAC Esti

New Member
Joined
May 12, 2020
Messages
11
Office Version
  1. 2010
Platform
  1. Windows
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.

Thanks in advance.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

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
Joined
May 12, 2020
Messages
11
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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 :unsure:
- 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) (y)

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
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 12, 2020
Messages
11
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 12, 2020
Messages
11
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Yongle,

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

Thanks,
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
will have some time tomorrow
 

HVAC Esti

New Member
Joined
May 12, 2020
Messages
11
Office Version
  1. 2010
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

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