/docs/community?hl=en
This content is likely not relevant anymore. Try searching or browse recent questions.
How to sum the numbers in colored cells
1
I have a google spreadsheet with numbers in colored cells (red and green). And I want a function of some kind that creates the sum of the numbers in the red colored cells. And also updates when I change the color of a cell.

The only function I could find out there doesn't work and wouldn't be able to automatically update too.
Details
Pinned
Locked
Latest Update Latest Updates (0)
Relevant Answer Relevant Answers (0)
All Replies (8)
Relevant Answer
Hello Flave,

This question gets asked a lot, and always boils down to this line of questions:
 
Why are the cells colored green or red?  That is, how did they get that way?
Did you do it manually? if so, based on what logic?
Or is there some conditional formatting that is making them red or green? if so what is the rule for it?
 
I think it's possible to solve this problem for you, but I and others here would greatly benefit from being able to see a sample of the problem.  Please check out this pinned post for the best way to do it.
 
 
Tip: Read the Tips post above, then check your sharing settings before posting - you want "Anyone with the link can Edit" 
 
Cheers,
Matt
marked this as an answer
Relevant Answer
Hi,

There is an addon called 'Custom Count and Sum'  containing 15 custom functions that extend the existing SUM, COUNT and AVERAGE functions to allow the user to perform the aggregations according to the format of the cells - background colour, font colour, and font style. There are also functions in the package that allow you to perform the aggregations across multiple sheets (within the same spreadsheet) without the need for complex formulas.

However, those custom functions will not automatically update when you change the background color of a cell. There is a workaround though that 'forces' a recalculation by pressing a menu-button.

If you want to explore that solution, let us know and we'll see how we can assist you.
marked this as an answer
Relevant Answer
Hi there,


I would like to explore that mentioned addon or any other way that might help me
marked this as an answer
Relevant Answer
Hi,

wow yes that's just what I need! Problem is, I need this for a spreadsheet at work. And I just now entered the formula and added the two scripts in that spreadsheet from work and it does sum the numbers and there's the new Update-Button. But when I click on "Force recalculation" the amount is not updated like it is in the example sheet. So I don't know where I went wrong

EDIT: Okay no, I found the mistake. It's working now! Thank you so so much!
marked this as an answer
Relevant Answer
My bad, forgot to tell you (as explained in the linked document in my previous post) that you need to create an extra tab, called 'HELPER'. In that tab, you only need cell A1. The rest of the columns and rows can be removed. This sheet/tab can be hidden. Then it should word exactly as in the spreadsheet you shared.
marked this as an answer
Relevant Answer
No that was not the problem, I understood that just fine :)
 I just added a cell number in the original formula when I should've only written the cell letter
marked this as an answer
Relevant Answer
Ok. Glad to hear it's working now.
marked this as an answer
This question is locked and replying has been disabled.
Discard post? You will lose what you have written so far.
Write a reply
10 characters required
Failed to attach file, click here to try again.
Discard post?
You will lose what you have written so far.
Personal information found

We found the following personal information in your message:

This information will be visible to anyone who visits or subscribes to notifications for this post. Are you sure you want to continue?

A problem occurred. Please try again.
Create Reply
Edit Reply
This will remove the reply from the Answers section.
Notifications are off
Your notifications are currently off and you won't receive subscription updates. To turn them on, go to Notifications preferences on your Profile page.
Report abuse
Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.

Go to the Legal Help page to request content changes for legal reasons.

Reported post for abuse
Unable to send report.
Report post
What type of post are you reporting?
Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.

Go to the Legal Help page to request content changes for legal reasons.

Reported post for abuse
Unable to send report.
This reply is no longer available.
/docs/threads
//accounts.google.com/ServiceLogin
You'll receive email notifications for new posts at
Unable to delete question.
Unable to update vote.
Unable to update subscription.
You have been unsubscribed
Deleted
Unable to delete reply.
Removed from Answers
Removed from Updates
Marked as Recommended Answer
Marked as Update
Removed recommendation
Undo
Unable to update reply.
Unable to update vote.
Thank you. Your response was recorded.
Unable to undo vote.
Thank you. This reply will now display in the answers section.
Link copied
Locked
Unlocked
Unable to lock
Unable to unlock
Pinned
Unpinned
Unable to pin
Unable to unpin
Marked
Unmarked
Unable to mark
Reported as off topic
Known Issue
Fixed
Marked Fixed
Unmarked Fixed
Unable to mark fixed
Unable to unmark fixed
/profile/0
false
Search
Clear search
Close search
Google apps
Main menu
Search Help Center
true
35
false