+ Reply to Thread
Results 1 to 17 of 17

Calculate the total of two different background colour figure cells in same column

  1. #1
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    208

    Calculate the total of two different background colour figure cells in same column

    Hello, I have a constantly growing spreadsheet and have been manually highlighting all the cells I want to add up to see the total and then manually input. This is taking a long time.

    All new data gets added as a new row so the total row is never going to remain fixed either.

    There are three colours on the sheet. Green, blue and orange.

    Can someone please tell me how I would instruct Excel to calculate the total value of green and blue in column H but ignore any in red?

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Calculate the total of two different background colour figure cells in same column

    This can't be done with a formula. You will need VBA - shall I move the thread for you?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    208

    Re: Calculate the total of two different background colour figure cells in same column

    Thank you, if that is the only way.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Calculate the total of two different background colour figure cells in same column

    It is the only way. Formula cannot 'read' cell formatting. This thread is now in the VBA section.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Calculate the total of two different background colour figure cells in same column

    Please post a sample w/book: instructions are in the yellow banner at top of the page.

    what determines the colour codes [as it may be possible to use formulae if these are definable]?
    Last edited by JohnTopley; 07-12-2021 at 08:44 AM.

  6. #6
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    208

    Re: Calculate the total of two different background colour figure cells in same column

    Thank you for your reply.

    I apply colours to the cells. New rows are added above the green totals row and start as orange. It would be great if newly added rows were orange by default and turned green when refund received date is added. Anything with a future received date should automatically be blue.

    Everything is organised by dates but should always stay in this colour order (green for refund, blue for extras so use future dates and orange for in progress).

    I would like for cells to automatically move from orange to the bottom of the green list once I set the colour and both received date and refund received date are present.

    I am also looking for a way for all the total rows at the bottom to keep track of the respective rows.

    Third from bottom (green) is how much something is bought for compared to how much it is sold for, second from bottom (red) is total amounts paid outstanding and the white row compares bought and outstanding to give a total figure.

    The difficult part is that I cannot define a cell range for the total rows at the bottom, because new cells get added and that means totals will not be correctly calculated.

    I have also been using sort, but having to run it manually every time I update. It would be much better if it refreshed automatically.

    Thank you for your help.
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Calculate the total of two different background colour figure cells in same column

    Perhaps the following may help.
    Conditional formatting is applied as follows:
    For green: =AND($G5<>"",$H5<>"")
    For blue: =AND($G5="",$H5<>"")
    For amber: =AND($G5<>"",$H5="")
    The formula for the costs in green is: =SUMIFS(Table2[Cost],Table2[Paid],"<>")
    The formula for costs in amber is: =SUMIFS(Table2[Cost],Table2[Paid],"")
    The formula for paid is: =SUM(Table2[Paid])
    Note that the formulas are placed in cells above the table.
    Note that the order of the rows doesn't affect the output of the formulas modeled on Sheet1 as compared to the corresponding values on the Example sheet.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  8. #8
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    208

    Re: Calculate the total of two different background colour figure cells in same column

    Hello Jete, thank you very much for your reply.

    It looks like a very good step in the right direction.

    Few things:

    In the example, the totals should be in rows 21:23 whereas these are figures that have been typed in and are added to the total in the top rows. Ideally the totals would be displayed at the bottom of the sheet in green, red and white, no matter how many rows are added.

    Bottom of the sheet (as shown in the example you attached, prior to Sheet1)
    Green row column G should calculate the total cost of green rows only, green row column H should calculate the total paid of green and blue rows.
    Red row column G should calculate the total cost of yellow rows only.
    White row column G should calculate the total cost of green and red rows.

    I hope this makes sense, thank you very much for your time.

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Calculate the total of two different background colour figure cells in same column

    Changes to the formulas and the formatting rules.
    The formula for the costs in green is: =SUMIFS(G$2:G20,H$2:H20,"<>")
    The formula for costs in amber is: =SUMIFS(G$2:G20,H$2:H20,"<>")
    The formula for Paid is: =SUM(H$2:H20)
    For the red row (word red is placed in cell A22): =$A2="Red"
    For the white row (word white is placed in cell A23): =$A2="white"
    Let us know if you have any questions.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    208

    Re: Calculate the total of two different background colour figure cells in same column

    Wow, thank you very much. It looks right now.

    The only thing missing that was mentioned originally is that something should remain orange until Refund Issued and Refund Received have a date inserted.

    "Everything is organised by dates but should always stay in this colour order (green for refund, blue for extras so use future dates and orange for in progress)."

    Looks like I was ambiguous about orange so understandably this wasn't incorporated. Sorry about that!

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Calculate the total of two different background colour figure cells in same column

    I am out of town so it will be a few days before I can work on this. Maybe one of the other contributors can help in the meanwhile.

  12. #12
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    208

    Re: Calculate the total of two different background colour figure cells in same column

    Hello Jete, hopefully you are back now. Not had any other responses.

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Calculate the total of two different background colour figure cells in same column

    I feel that there may be a way to sort the rows into colors using helper columns which will sometimes require resorting after new entries are made.
    If that is something you can work with then:
    1. The first helper column (Column1) is populated using: =IF(COUNT(G2:I2,P2:Q2)=5,1,IF(AND(ISNUMBER(H2),ISNUMBER(I2)),2,3))
    2. The second helper column (Column2) is populated using: =SUM([@Column1],(SUMPRODUCT(([Column1]=[@Column1])*([Received]<[@Received]))+1)/10)
    3. The conditional formatting rules are changed so that they are similar to: =$S2=3 (orange)
    4. The table is sorted according to values in Column2 (smallest to largest)
    Note that after inserting a new row the formulas in Columns 1 and 2 will need to be copied down in order for the conditional formatting to take effect.
    Note the formulas in Columns 1 and 2 should not be copied to the green summation row or below.
    Let us know if you have any questions.
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    208

    Re: Calculate the total of two different background colour figure cells in same column

    Helo Jete, thank you very much. Is there any VBA that could be used to replicate the copy down requirement when a new row is inserted and automatic resorting of the helper columns?

  15. #15
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Calculate the total of two different background colour figure cells in same column

    I can't help wondering, if you are considering using VBA, why don't you add an Index column and/or a date/time stamp.

    A structured Table will automatically copy formulae and formatting to new rows. And you could sort the data using the index, the time stamp, the calculated value.

    If you put a start and end index in a couple of cells, or equally a start and end time stamp, you could use Conditional Formatting to highlight records between those limits. And you CAN filter based on colours and the total row will only include visible rows.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  16. #16
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Calculate the total of two different background colour figure cells in same column

    The alternative is to move the Totals rows outside of the table.
    Then col G green:
    Please Login or Register  to view this content.
    Col H green
    Please Login or Register  to view this content.
    col G red:
    Please Login or Register  to view this content.
    col G white:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by protonLeah; 07-31-2021 at 05:10 PM.
    Ben Van Johnson

  17. #17
    Forum Contributor
    Join Date
    11-08-2014
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    208

    Re: Calculate the total of two different background colour figure cells in same column

    It turns out the solution was very simple, thank you Leah!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA to sum cells by background colour
    By gasdesign in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-28-2019, 08:48 AM
  2. [SOLVED] format a cell background colour based on a manual background colour of an adjacent cell
    By Nampara in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-24-2018, 04:03 PM
  3. [SOLVED] Search cells based on student name, cell background colour and return a tally for colour
    By drof_06 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-26-2016, 04:31 AM
  4. Insert/Update particular column based on background colour of another column
    By onenessboy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-01-2012, 03:28 AM
  5. [SOLVED] sum up cells by background colour
    By Mengo85 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-16-2012, 01:27 PM
  6. calculate which cells in column A will give me the total of column
    By Ken in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-06-2005, 03:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1