software

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: www.excelforum.com/member.php...oamp;userid=34605
View this thread: www.excelforum.com/showthread...hreadid=543775RMax

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?

RegardsAlex

quot;RMaxquot; wrote:

gt;
gt; I have a spreadsheet that lists each job we have on continuous rows. I
gt; highlight in yellow the rows that contain the jobs that we have not
gt; recieved payment for. Periodically, I print a copy of the spreadsheet
gt; and manually add the 'yellow' jobs together. Is there a formula that
gt; will add the yellow amounts, only? Maybe a 'sumif' function? Thanks.
gt;
gt;
gt; --
gt; RMax
gt; ------------------------------------------------------------------------
gt; RMax's Profile: www.excelforum.com/member.php...oamp;userid=34605
gt; View this thread: www.excelforum.com/showthread...hreadid=543775
gt;
gt;


I'm not familiar with VBA. However, I'm open to all options. Thanks.--
RMax
------------------------------------------------------------------------
RMax's Profile: www.excelforum.com/member.php...oamp;userid=34605
View this thread: www.excelforum.com/showthread...hreadid=543775A non-VBA approach would be to add a new column, and then use that column to
enter an quot;Xquot; for any rows that you want to highlight. You can use
Conditional Formatting in your quot;Jobquot; column to create the highlight. Then,
you can use SUMIF to get your total of quot;highlightedquot; jobs by checking the new
column for the presence of an quot;Xquot;.

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 quot;Xquot; for any rows that
should be highlighted.

Select colummns A and B, then from the Format Menu, select Conditional
Formatting. Change quot;Cell Value Isquot; to quot;Formula Isquot; and enter the formula:

=$C1=quot;Xquot;

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,quot;Xquot;,B1:B100)

You should now have a total of all cells in column B that are highlighted.

HTH,
Elkarquot;RMaxquot; wrote:

gt;
gt; I'm not familiar with VBA. However, I'm open to all options. Thanks.
gt;
gt;
gt; --
gt; RMax
gt; ------------------------------------------------------------------------
gt; RMax's Profile: www.excelforum.com/member.php...oamp;userid=34605
gt; View this thread: www.excelforum.com/showthread...hreadid=543775
gt;
gt;

If, instead of an quot;Xquot; you would enter a quot;1quot; in that new column, you could use
a formula like quot;=SUMPRODUCT(J1:J100,L1:L100)quot; where J is the column with
invoices and L is the column with 1's. Or conversely, what I do is enter a
quot;1quot; when the amount is received, so to get a total receivables from the
formula listed above. Then your outstandings would simply be
quot;=SUM(J1:J100)-SUMPRODUCT(J1:J100,L1:L100)quot; (total invoices - total received
= total outstanding).

Of course you'd have to adjust the conditional formula that generates the
yellow. highlighting.

quot;Elkarquot; wrote:

gt; A non-VBA approach would be to add a new column, and then use that column to
gt; enter an quot;Xquot; for any rows that you want to highlight. You can use
gt; Conditional Formatting in your quot;Jobquot; column to create the highlight. Then,
gt; you can use SUMIF to get your total of quot;highlightedquot; jobs by checking the new
gt; column for the presence of an quot;Xquot;.
gt;
gt; For example, lets say your Jobs are stored in Column A, and amounts in
gt; Column B. You would add a new column C, and enter an quot;Xquot; for any rows that
gt; should be highlighted.
gt;
gt; Select colummns A and B, then from the Format Menu, select Conditional
gt; Formatting. Change quot;Cell Value Isquot; to quot;Formula Isquot; and enter the formula:
gt;
gt; =$C1=quot;Xquot;
gt;
gt; Then choose your formatting (yellow background). Click OK. Now the
gt; appropriate rows should highlight automatically.
gt;
gt; Then for your total, use this formula:
gt;
gt; =SUMIF(C1:C100,quot;Xquot;,B1:B100)
gt;
gt; You should now have a total of all cells in column B that are highlighted.
gt;
gt; HTH,
gt; Elkar
gt;
gt;
gt; quot;RMaxquot; wrote:
gt;
gt; gt;
gt; gt; I'm not familiar with VBA. However, I'm open to all options. Thanks.
gt; gt;
gt; gt;
gt; gt; --
gt; gt; RMax
gt; gt; ------------------------------------------------------------------------
gt; gt; RMax's Profile: www.excelforum.com/member.php...oamp;userid=34605
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=543775
gt; gt;
gt; gt;

RMax

For a VBA solution try this.

(1) In your spreadsheet that lists the jobs press ALT F11 (This open VB
editor)
(2) Select lt;Insertgt;lt;Modulegt; (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 quot;Total payments outstanding: quot; amp; Format(sum, quot;$0.00quot;)
End Sub

(4) Now close VB editor (you should now just have the spreadsheet open)
(5) On the spreadsheet go to lt;Viewgt;lt;Tollbarsgt;lt;Formsgt;.
(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 quot;Assign Macroquot; 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, quot;£0.00quot;).

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

quot;RMaxquot; wrote:

gt;
gt; I have a spreadsheet that lists each job we have on continuous rows. I
gt; highlight in yellow the rows that contain the jobs that we have not
gt; recieved payment for. Periodically, I print a copy of the spreadsheet
gt; and manually add the 'yellow' jobs together. Is there a formula that
gt; will add the yellow amounts, only? Maybe a 'sumif' function? Thanks.
gt;
gt;
gt; --
gt; RMax
gt; ------------------------------------------------------------------------
gt; RMax's Profile: www.excelforum.com/member.php...oamp;userid=34605
gt; View this thread: www.excelforum.com/showthread...hreadid=543775
gt;
gt;

Hi RMax,

See xlDynamic's ColourCounter page at:

www.xldynamic.com/source/xld.ColourCounter.html---
Regards,
Norman
quot;RMaxquot; gt; wrote in message
...
gt;
gt; I have a spreadsheet that lists each job we have on continuous rows. I
gt; highlight in yellow the rows that contain the jobs that we have not
gt; recieved payment for. Periodically, I print a copy of the spreadsheet
gt; and manually add the 'yellow' jobs together. Is there a formula that
gt; will add the yellow amounts, only? Maybe a 'sumif' function? Thanks.
gt;
gt;
gt; --
gt; RMax
gt; ------------------------------------------------------------------------
gt; RMax's Profile:
gt; www.excelforum.com/member.php...oamp;userid=34605
gt; View this thread: www.excelforum.com/showthread...hreadid=543775
gt;