?
Solved

Excel: Is there a COUNTA formula that returns a count referencing cell color?

Posted on 2016-08-15
5
Medium Priority
?
38 Views
Last Modified: 2016-09-03
Hello:

I'm wondering if there is a count formula out there that can be written, where it counts cells filled with text, and if shaded green it counts +1, and if shaded red it counts -1.  

For example, if Column A had 20 blank cells not shaded at all, an additional 4 cells filled with text and those cells shaded green, and an additional 7 cells filled with text and those cells shaded red, the resultant COUNTA total returned would be -3.

Thanks in advance!
0
Comment
Question by:Tim Jackoboice
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 27

Accepted Solution

by:
ProfessorJimJam earned 2000 total points (awarded by participants)
ID: 41756513
you can do this with User Defined Function.

please see attached file which has the UDF in its module and also the example.
EE.xlsm
1
 
LVL 7

Expert Comment

by:Knightsman
ID: 41757019
You cannot do this with a formula

This is a walkthrough, setting this up:
https://support.microsoft.com/en-us/kb/2815384
0
 

Author Comment

by:Tim Jackoboice
ID: 41757296
Prof JimJam:  Thanks ... but when I copy your example to my master spreadsheet where I want to use this, I get the #NAME? error.
0
 
LVL 27

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 2000 total points (awarded by participants)
ID: 41757406
yOU need to copy the module as well not just the sheet. , perhaps your are just copying the sheet.

There is a code in he file I shared and you need to copy that code and put it in your master workbook . To learn how to do that please see http://www.rondebruin.nl/win/code.htm

Also when you save the master workbook it should be in format of macro enabled workbook , you can save as after you put the code and then. Select a macro enabled well book or excel binary work lol in order for the code to be able to work , UDF and macro disappear if the file format is just standard xlsx excel workbook .
0
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 41782777
The solution is provided along with follow up question answered.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question