Hypothetically yes... sort of (see corrected formulae below) But... each cell is defined as a smallint so you can fit a maximum of 32,767 characters in a cell.
I can't imagine that you are likely to breach that but it's just a bit of extra info.
To save you going in and changing the cells as your criteria changes it might be a good idea to point to value fields rather than specific items so rather than saying 8.25, 7.25 etc. point to a set of fields.
For example, just a simple case but I would include at least 10 options.
Column A1 : 7.25
Column A2 : 8.25
In the formulae that you have :
COUNTIF(AC18:AC30,a1)+COUNTIF(AC18:AC30,a2)
string 10 of those together and for any cell that you don't need to count enter the word NULL (or anything else that you fancy) in the search cell.
HTH,
Shaun.
p.s. just thought to mention. When you are refering to cells rather than values the reference should not be in quotes
-- Edited by Shamus on Wednesday 16th of January 2013 12:37:33 PM
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
Unless you fancy learning Visual Basic for Applications your best bet may be to look at the condition that set the colour in the first place and as well as setting the colour add an indicator in another, none displayed column and then the count sums the entries in the column that have something in them.
Sorry the answers not what you are looking for,
kind regards,
Shaun.
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
Ooh, that appeared straight away... Wonder if that means that the site is now fixed?
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
Hi Shaun, everywhere points to as you say using VBA. Is it possible to use a count formula to just count cells ina range that have data in them, so some will have 8.25 and sum will have 7.75, I just want to know how many in a column have just this data in.
Count a specific value : =COUNTIF(AC18:AC30,">0") the criteria must be in quotes so in your example that could be "=8.25"
Count non blank cells : =COUNTA(AC18:AC30)
hope that helps,
Shaun.
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
Thanks Shaun, I should be able to use as many criteria as I like in one formula shouldn't I? IE =COUNTIF(AC18:AC30,"8.25",(AC18:AC30,"7.75")) or there or thereabouts.
Thats perfect thanks Shaun. I have a spread sheet where in a column I can put each days hours per person and it will calculate the weekly gross dependant on what hourly rate is in a particular cell. Holidays are in a column next to normal work hours and I just want it to give me a running total of holidays taken without having to manually count the days, this is where the colour comes in as all the holiday and bank holiday entries were in a coloured cell and I wanted to count them, counting the cells in a column with a value performs a similar task.
This probably won't help you Steve, but I have a VBA module that sums the figures in a range of cells based on their colour. If anyone is interested?
I'm interested please
I did find these online, however i havent a clue what to do with them It appears you have to put this script in somewhere and that allows you to look for colours but how/where do you put the script in in the first place
-- Edited by Rhianrach on Thursday 17th of January 2013 06:31:55 PM
If you like I can send you an Excel workbook with the VBA module already in it. Trouble is it will be an Excel 2003 spreadsheet (as I'm behind the times), so if you're using 2007 or 2010 not sure it will work, but it might.
In 2003 you open a new workbook, go to the Tools menu - Macro - Visual Basic editor. In the Project - VBA Project box on the left hand side there will be a list of the sheets in the workbook and also This Workbook. If you right click on This Workbook - Insert - Module, a Module box appears in the main window.
This is where the code for the Sumcolor function is pasted. You then save it as whatever you want the workbook to be called and close the VBA window. You are then left with the Excel workbook which contains the code for the function.
To use the function in the workbook the formula is =sumcolor(K1,(range of cells)). You will have to create a cell (in this case K1, but can be any cell you choose) with the particular colour in it first. Then you highlight the range of cells you wish to sum and press enter. Oh, and there must be a comma after the K1 before highlighting the cell range.
You can also use Insert - Function from the Toolbar and it will appear in the User defined category in the "Or select a category" box.
The only drawback with this function is that once you have highlighted the cells with the colour you have to press Ctrl + Alt + F9 to refresh it. It doesn't add up automatically as you enter the colour.
As I said, these instructions are for Excel 2003, but it must be similar in 2007/2010.
I have attached a Word document with the code in it. All you have to do is to copy it and then paste it into the VBA module.
Just read all this back before posting.....sounds complicated!! If you wish me to email you a workbook with it already in, please let me know.
Happy experimenting!
Edit: To adjust formatting.
-- Edited by Stardoe on Thursday 17th of January 2013 08:32:12 PM
It appears you have to put this script in somewhere and that allows you to look for colours but how/where do you put the script in in the first place 
