Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula that will add only highlighted rows?
I have a spreadsheet that lists each job we have on continuous rows. I highlight in yellow the rows that contain the jobs that we have not recieved payment for. Periodically, I print a copy of the spreadsheet and manually add the 'yellow' jobs together. Is there a formula that will add the yellow amounts, only? Maybe a 'sumif' function? Thanks. -- RMax ------------------------------------------------------------------------ RMax's Profile: http://www.excelforum.com/member.php...o&userid=34605 View this thread: http://www.excelforum.com/showthread...hreadid=543775 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula that will add only highlighted rows?
RMax
As far as I know there isn't any built-in Excel function that will sum values based upon the colour format of a cell/row. However, it can be doen with VBA if you won't to take that route. I can do somehing pretty easily if you wnat that? Regards Alex "RMax" wrote: I have a spreadsheet that lists each job we have on continuous rows. I highlight in yellow the rows that contain the jobs that we have not recieved payment for. Periodically, I print a copy of the spreadsheet and manually add the 'yellow' jobs together. Is there a formula that will add the yellow amounts, only? Maybe a 'sumif' function? Thanks. -- RMax ------------------------------------------------------------------------ RMax's Profile: http://www.excelforum.com/member.php...o&userid=34605 View this thread: http://www.excelforum.com/showthread...hreadid=543775 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula that will add only highlighted rows?
I'm not familiar with VBA. However, I'm open to all options. Thanks. -- RMax ------------------------------------------------------------------------ RMax's Profile: http://www.excelforum.com/member.php...o&userid=34605 View this thread: http://www.excelforum.com/showthread...hreadid=543775 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula that will add only highlighted rows?
A non-VBA approach would be to add a new column, and then use that column to
enter an "X" for any rows that you want to highlight. You can use Conditional Formatting in your "Job" column to create the highlight. Then, you can use SUMIF to get your total of "highlighted" jobs by checking the new column for the presence of an "X". For example, lets say your Jobs are stored in Column A, and amounts in Column B. You would add a new column C, and enter an "X" for any rows that should be highlighted. Select colummns A and B, then from the Format Menu, select Conditional Formatting. Change "Cell Value Is" to "Formula Is" and enter the formula: =$C1="X" Then choose your formatting (yellow background). Click OK. Now the appropriate rows should highlight automatically. Then for your total, use this formula: =SUMIF(C1:C100,"X",B1:B100) You should now have a total of all cells in column B that are highlighted. HTH, Elkar "RMax" wrote: I'm not familiar with VBA. However, I'm open to all options. Thanks. -- RMax ------------------------------------------------------------------------ RMax's Profile: http://www.excelforum.com/member.php...o&userid=34605 View this thread: http://www.excelforum.com/showthread...hreadid=543775 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula that will add only highlighted rows?
If, instead of an "X" you would enter a "1" in that new column, you could use
a formula like "=SUMPRODUCT(J1:J100,L1:L100)" where J is the column with invoices and L is the column with 1's. Or conversely, what I do is enter a "1" when the amount is received, so to get a total receivables from the formula listed above. Then your outstandings would simply be "=SUM(J1:J100)-SUMPRODUCT(J1:J100,L1:L100)" (total invoices - total received = total outstanding). Of course you'd have to adjust the conditional formula that generates the yellow. highlighting. "Elkar" wrote: A non-VBA approach would be to add a new column, and then use that column to enter an "X" for any rows that you want to highlight. You can use Conditional Formatting in your "Job" column to create the highlight. Then, you can use SUMIF to get your total of "highlighted" jobs by checking the new column for the presence of an "X". For example, lets say your Jobs are stored in Column A, and amounts in Column B. You would add a new column C, and enter an "X" for any rows that should be highlighted. Select colummns A and B, then from the Format Menu, select Conditional Formatting. Change "Cell Value Is" to "Formula Is" and enter the formula: =$C1="X" Then choose your formatting (yellow background). Click OK. Now the appropriate rows should highlight automatically. Then for your total, use this formula: =SUMIF(C1:C100,"X",B1:B100) You should now have a total of all cells in column B that are highlighted. HTH, Elkar "RMax" wrote: I'm not familiar with VBA. However, I'm open to all options. Thanks. -- RMax ------------------------------------------------------------------------ RMax's Profile: http://www.excelforum.com/member.php...o&userid=34605 View this thread: http://www.excelforum.com/showthread...hreadid=543775 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula that will add only highlighted rows?
RMax
For a VBA solution try this. (1) In your spreadsheet that lists the jobs press ALT + F11 (This open VB editor) (2) Select <Insert<Module (This will insert a module) (3) In that module paste the following Sub SumPayments() Dim cl As Range Dim sum As Double For Each cl In Selection If cl.Interior.ColorIndex = 6 Then sum = sum + cl.Value End If Next cl MsgBox "Total payments outstanding: " & Format(sum, "$0.00") End Sub (4) Now close VB editor (you should now just have the spreadsheet open) (5) On the spreadsheet go to <View<Tollbars<Forms. (6) From the floating panel that is now present there is a 'button' icon (hover over it and 'Button' will appear) (7) Click that button and then on the spreadsheet left click the mouse and hold and drag the outline of the button (you should now have a grey button on your desk and a dialog box "Assign Macro" should be present) (8) In that dialog box should be 'SumPayments'. Highlight it and select OK. (9) Now test the button works. Suppose the payments you want to add are in column C and you have highlighted the jobs in yellow that have not paid. Select all of column C with your mouse e.g. if you have 100 jobs select C1:C100 and then click the button. (10) A msgbox shoiuld appear that totals all the jobs in yellow. Some notes to consider: (A) The VBA works by summing any cell in yellow from the range that you have selected so it doesn't matter if you are not using column C as in my example. (B) You can move the button to any place on the worksheet that you like for convenience. (C) I am using Excel 2002 and on my colour palette I have selected yellow as opposed to pale yellow. In the code I gave you the colorindex for this is 6. Make sure you are using the same yellow. (D) I have set the format of message box to show the value as dollars ($). If you want to change that to pounds (£) for example, then in the last line of the code change to FORMAT(sum, "£0.00"). Let me know if this solution is satisfactory. I read the other posts and they are good non-VBA solutions if you prefer that. It won't hurt my feelings... Alex "RMax" wrote: I have a spreadsheet that lists each job we have on continuous rows. I highlight in yellow the rows that contain the jobs that we have not recieved payment for. Periodically, I print a copy of the spreadsheet and manually add the 'yellow' jobs together. Is there a formula that will add the yellow amounts, only? Maybe a 'sumif' function? Thanks. -- RMax ------------------------------------------------------------------------ RMax's Profile: http://www.excelforum.com/member.php...o&userid=34605 View this thread: http://www.excelforum.com/showthread...hreadid=543775 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula that will add only highlighted rows?
Hi RMax,
See xlDynamic's ColourCounter page at: http://www.xldynamic.com/source/xld.ColourCounter.html --- Regards, Norman "RMax" wrote in message ... I have a spreadsheet that lists each job we have on continuous rows. I highlight in yellow the rows that contain the jobs that we have not recieved payment for. Periodically, I print a copy of the spreadsheet and manually add the 'yellow' jobs together. Is there a formula that will add the yellow amounts, only? Maybe a 'sumif' function? Thanks. -- RMax ------------------------------------------------------------------------ RMax's Profile: http://www.excelforum.com/member.php...o&userid=34605 View this thread: http://www.excelforum.com/showthread...hreadid=543775 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting all rows of data that have a value in a particular column | Excel Discussion (Misc queries) | |||
Is it possible? | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
apply formula to other rows ... | Excel Discussion (Misc queries) | |||
formula to use when number of rows changes dynamically | Excel Worksheet Functions |