[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 45
  • 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 Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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