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.
 
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
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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?
 
Upvote 0
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
 
Upvote 0
I already have this working as a macro, next step is creating a robust UDF that can be used as a formula
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
Yongle,

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

Thanks,
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

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
Back
Top