Is there a formula that will add only highlighted rows?

[RMax]

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.

 

[Alex]

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]

I'm not familiar with VBA. However, I'm open to all options. Thanks.

 

[Elkar]

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

 

[andy62]

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.

 

[Alex]

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

 

[Norman Jones]

Hi RMax,

See xlDynamic's ColourCounter page at:

http://www.xldynamic.com/source/xld.ColourCounter.html