Results 1 to 6 of 6

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

  1. #1
    Neophyte sosh's Avatar
    Join Date
    Jan 2018
    Posts
    4
    Articles
    0
    Excel Version
    2013

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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...
    Click image for larger version. 

Name:	SoSh.png 
Views:	16 
Size:	61.0 KB 
ID:	7717

  2. #2
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,469
    Articles
    0
    Excel Version
    2010 on Xubuntu
    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
    Thank you Ken for this secure forum.

  3. #3
    Neophyte sosh's Avatar
    Join Date
    Jan 2018
    Posts
    4
    Articles
    0
    Excel Version
    2013

    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.
    Attached Files Attached Files

  4. #4
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,469
    Articles
    0
    Excel Version
    2010 on Xubuntu
    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
    Thank you Ken for this secure forum.

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,480
    Articles
    0
    Excel Version
    365
    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).
    Attached Files Attached Files

  6. #6
    Neophyte sosh's Avatar
    Join Date
    Jan 2018
    Posts
    4
    Articles
    0
    Excel Version
    2013
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •