Take the 2-minute tour ×
Ask Different is a question and answer site for power users of Apple hardware and software. It's 100% free, no registration required.

I have a spreadsheet of open projects with payments for each milestone. Right now I set a cell to red if the client still owes, green if the client paid, and no fill if we havent reached the milestone yet. What I would like to do is get a sum on all red cells and another sum on all green cells. My hope is to make it easy to see how much money has been paid and how much is owed.

share|improve this question
add comment

2 Answers

Here's a better option:

  1. Add an 'Account Status' column
  2. Select the entire column and set the cell format to [Pop-Up Menu] with options for 'Unfinished', 'Paid', '30 Days', '60 Days', '90+ Days' (or whatever options you want)
  3. Select the entire column and set some conditional rules to color the cell according to the text in the cell (e.g. a green for [Text Is 'Paid'], a yellow for [Text Is '30 Days'], etc.)
  4. Add some summary cells with COUNTIF formulas for each of your Pop-Up menu options ([=COUNTIF(Account Status,"Paid")], [=COUNTIF(Account Status,"30 Days")], etc.)

Note that this will color ONLY the cells in the 'Account Status' column. There are some tricks to color the entire row, but this should be enough to catch your eye.

You can also use AppleScript if you need more complex summaries, but scripts cannot run automatically, where conditional rules do.

share|improve this answer
add comment

This isn't the way to sum colours, you cant do this as the colour does not contain information that would be inputted into the cell,

A workaround however is to add three columns, these would be titled Paid, Owed, Non complete. You can then add a radio button to all of the cells in these columns.

You can then sum the total of radio buttons checked in each column to give you running totals.

share|improve this answer
1  
Ugh, can you post a screenshot for this (or a link to one, somebody with edit rights can then add it to the answer)? –  patrix Nov 23 '11 at 14:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.