?
Solved

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

Posted on 2016-08-15
5
Medium Priority
?
34 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 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: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 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 26

Expert Comment

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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 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