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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Yongle

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

HVAC Esti

New Member
Joined
May 12, 2020
Messages
11
Office Version
  1. 2010
Platform
  1. Windows
Here is a link to the excel file that has what I'm trying to accomplish. Hopefully this helps.


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

VBA Code:
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
 

HVAC Esti

New Member
Joined
May 12, 2020
Messages
11
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Yongle,

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

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
I have downloaded it - will look at it tomorrow
 

HVAC Esti

New Member
Joined
May 12, 2020
Messages
11
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Yongle,

Thanks, I really appreciate it.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Sheet "Main" requires strict discipline applied to it for any auto-summation to function as required :unsure:

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 :rolleyes:
Excel and VBA cannot do that o_O

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
 
Last edited:

Yongle

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

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

Watch MrExcel Video

Forum statistics

Threads
1,130,130
Messages
5,640,300
Members
417,135
Latest member
zeusmining

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