Solved

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

Posted on 2016-08-15
5
30 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:Cactus1994
[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 26

Accepted Solution

by:
ProfessorJimJam earned 500 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:Cactus1994
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 26

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 500 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 26

Expert Comment

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

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

724 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