Needs for:Formula that returns the sum of "font colored cells" for a subgroup company

sosh

New member
Joined
Jan 12, 2018
Messages
4
Reaction score
0
Points
0
Excel Version(s)
2013
Needs for:Formula that returns the sum of "font colored cells" for a subgroup company

Hi all,
I have a list of companies(up to 500) that have costumers in subgroup. This costumers change their deposit everyday. Therefore every person(costumer) has two number, one, its deposit and two its change of deposit. Companies have a specific background color and change of deposit have specific font color(green or red). I want a formula that returns the total change of company deposit in front of its name. I attach a picture of my question.
Regards...
SoSh.png
 
Hi and welcome

1. How is the background colored? Conditional formatting or manually?
2. Please post a sample sheet ( edit post or reply - click "Go advanced" and " manage attachments"). Images are nice to look at but impossible to work with
 
Reply to "PMPecoflyer"

Hi again.
1- The background color is created by a web service(output of web service) and it isn't conditionally or manually.
2- I attached the sample file to the reply too.
Thanks a lot.
 

Attachments

  • temp forum.xlsx
    13.1 KB · Views: 8
In this case I am transferring your thread to the programming section as I don't think formulas ( without UDF's eventually) can retrieve the background colors
 
Not especially easy, but with the help of a helper column it can be done:

  1. Autofilter columns A:B (make sure you're not autofiltering all the way down to the bottom of the sheet, but make sure the autofilter includes all your data)
  2. Filter column B: Filter by Color, then in the Filter by Font Color, click on the green oblong.
  3. Select column C (not the header) to the bottom of your data and…
  4. type a lower case x, hold the CTRL key down and press Enter (this will put an x in all the visible cells)
  5. Again Filter column B: Filter by Color, then in the Filter by Font Color, click on the red oblong.
  6. Select column C (not the header) to the bottom of your data and…
  7. type a lower case x, hold the CTRL key down and press Enter (this will put an x in all the visible cells)
  8. For a 3rd time, Filter column B: Filter by Color, then in the Filter by Cell Color, and choose the Company name background colour.
  9. Select column C (this time include the header since it's a company name) to the bottom of your data and…
  10. type a lower case y, hold the CTRL key down and press Enter (this will put an y in all the visible cells)
  11. Remove the Autofilter
In cell D1 enter this formula:
=IF(C1="y",SUMIF(C1:INDEX(C1:C$25,MATCH("y",C2:C$25,0)),"x",B1:B$25),"")
then copy it down to the bottom of your data.
Note that this formula contains 3 instances of 25 which is the last row of data on your example sheet; these should be changed to whatever is the last row of data on your actual sheet.
This has been implemented on sheet test (2) of the attached.

QED

You can, if you want, then copy column D and paste to column C as Values, then delete column D.



Another possibility is in sheet test of the attached. Here a formula such as:
=SUMPRODUCT((FontColour(B2:B5)=32768)+(FontColour(B2:B5)=255),B2:B5)
is being used.
FontColour is not a built in worksheet function but a user-defined function.
The problem with this one is that the formula can't just be copied down, because your companies have different numbers of people. 500 companies is a lot to have to do this manually for, but a little macro could do it - come back if you want to know more.

For those interested, here's the code for that user-defined function:
Code:
Function FontColour(rng)
Clclr = rng
If IsArray(Clclr) Then
  For i = LBound(Clclr) To UBound(Clclr)
    For j = LBound(Clclr, 2) To UBound(Clclr, 2)
      Clclr(i, j) = rng(i, j).Font.Color
    Next j
  Next i
Else
  Clclr = rng.Font.Color
End If
FontColour = Clclr
End Function
This function returns an array, so it can be used in array-aware built-in functions and in array-entered formulae.
Used thus:
=FontColour(B13)
it will return the numeric font colour (32768 for green and 255 for red).
 

Attachments

  • ExcelGuru8725temp forum.xlsm
    19.9 KB · Views: 9
Hi guys.
Thanks for detailed description. It was tricky, smart and useful. If you have a plan to visit Mashhad(Iran), I'm glad to be your host.
This is my contact information:
ss.soroush.ss@gmail.com
+989352627904
 
Back
Top