Sumif and Colorfunction Colaboration.

[HVAC Esti]

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]

Welcome to the forum

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.

We cannot see what you are seeing and are likely to guess incorrectly wasting your time and ours
- please supply typical non-secret sample data which does not breach the Official Corporate Secrets Act (perhaps using XL2BB)
- it need not ressemble your corporate data at all , just make it useful and relevant so that you can use the solution
- supply whatever illustrates your problem and requirements
- also show how you are combining SumIf and ColorFunction

ColorFunction is not out of the standard Excel box
- it is a UDF or VBA function that you have not shared but want help with
- there are several floating around with that name and we have no idea which one you may be using
- please post the function to elimate guesswork
- to post the code, click on <vba/> icon and paste code between the code tags

many thanks

 

[HVAC Esti]

Here is a link to the excel file that has what I'm trying to accomplish. Hopefully this helps.

SumifColorfunction.xls

Shared with Dropbox

www.dropbox.com

Here is the VB code I'm using. my company really likes how it works. I'm not sure where I found it anymore.

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

 

[Yongle]

Here is the VB code I'm using. my company really likes how it works. I'm not sure where I found it anymore

Here, I think ...

Count or Sum Cells That Have a Specified Fill Color : Microsoft Excel

Using a bit of code, you can easily SUM or COUNT cells whose fill color was specified manually.

www.brainbell.com

 

[HVAC Esti]

Yongle,

Thanks that might be. Are you able to DL the file from drop box and understand what I'm trying to do?

 

[Yongle]

I have downloaded it - will look at it tomorrow

 

[HVAC Esti]

Yongle,

Thanks, I really appreciate it.

 

[Yongle]

Sheet "Main" requires strict discipline applied to it for any auto-summation to function as required

There is an inconsistency in "Main" as you already know .....

Put this formula into N5 on "Main" and copy down to N323
=IF(SUM($L5:$M5)=0,"",IFERROR(LOOKUP(999,A$5:A5),""))

Your eyes saw the inconsistency and you fudged the formula in the other sheet to compensate
Excel and VBA cannot do that

How are you going to avoid that type of problem to prevent the numbers being summed against the wrong line?

It would be easist if column A in main ALWAYS contain a "row number" if either L or M contain a value

 

[Yongle]

AS mentioned above .. it would be easist if column A in main ALWAYS contains a "row number" if either L or M contain a value
- prevents inconsistency
- function can lookup the value to make summing easier

Are you prepared to do that ?

 

[HVAC Esti]

I am prepared to put a "row number" in every cell in column A if that is what is needed.

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