[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 41
  • Last Modified:

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

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
Tim Jackoboice
Asked:
Tim Jackoboice
  • 3
2 Solutions
 
ProfessorJimJamCommented:
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
 
KnightsmanCommented:
You cannot do this with a formula

This is a walkthrough, setting this up:
https://support.microsoft.com/en-us/kb/2815384
0
 
Tim JackoboiceOwnerAuthor Commented:
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
 
ProfessorJimJamCommented:
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
 
ProfessorJimJamCommented:
The solution is provided along with follow up question answered.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now