Count only items in cell with black font

[drewship]

Hello all

I found this formula and it works great, but I need to count only items with black font. We use red font to identify lost items so don't want to count them as part of our inventory. Any help would be appreciated!!

Thanks,
Andrew

Formula to count the number of words separated by a character in a cell
The following formula counts the number of words in a cell that are separated by a specific character:

Please Login or Register  to view this content.

where <cell_ref> is the cell reference and <char> is the character separating the words.

My formula:

Please Login or Register  to view this content.

[StephenR]

I think you'd need VBA to do that. Though perhaps not if the font colour is determined by conditional formatting?

[Richard Buttrey]

Hi,

Excel doesn't provide standard functions to analyse cells by colour. You'll need to resort to VBA and test with

Please Login or Register  to view this content.

HTH

[drewship]

Thanks StephenR and Richard. I don't mind using VBA, but I don't know how to convert the formula to VBA....or does it need to be converted? I will try to use it as it is written and see if I can come up with something.

Andrew

[Richard Buttrey]

Hi Andrew,

It's arguably even simpler syntax in VBA, simply

Please Login or Register  to view this content.

obviously have the macro detect the actual A1 range and change the * character as appropriate

Rgds

[drewship]

The following is what I have cobbled together. I am not very good with VBA but it at least looks to me like it would do what I need. The count of black font items separated by , (comma) would be displayed in column F of the current row. Of course the formula doesn't work here as it is written, so help tweaking this would be terrific!!.

Please Login or Register  to view this content.

Richard, I saw your post but I am not sure I understand what you are talking about since I do not want to replace anything.

[drewship]

Bumping this back into view. Does anyone know of a way to do this or can you point me to a reference?

Thanks,
Andrew

[StephenR]

Andrew - can you clarify what you're trying to do - perhaps attach a sample workbook? Is there a mixture of colours in a cell or are they always the same?

[DonkeyOte]

If you want to do this as a Function...

Please Login or Register  to view this content.

called from cell along lines of

Please Login or Register  to view this content.

obviously you could if preferred set the colour based on the font colour of another cell etc...

Perhaps worth adding that altering font colour manually is not a Volatile action...

[drewship]

StephenR, I have uploaded an example of what I need. The formula in column F counts every set of numbers separated by a comma. I need to count only the sets with black font and the colors are always black or red.

DonkeyOte, Not familiar with functions but will take a look and see if I can get your code to work.

Thanks to both of you!!

[JeanRage]

Hi,

With all the data in a single cell ... you are facing a HUGE challenge ...
Life would a lot easier for you with separate cells for each game ...

HTH

[drewship]

Hello JeanRage. I agree that it would be easier if the serial numbers were separated into individual rows, but the project I am working on requires getting this information from several sources, each with their own preferred method of tracking this information. Hopefully, I will be able to use a series of code blocks to clean/sort/count all of this at one time every month when I receive it.

Thanks,
Andrew

[StephenR]

Picking up on JeanRage's point, you could automate Text to Columns, and then use some simple code for the rest.

[drewship]

DonkeyOte, I have attached the test workbook with your function included. I think I entered everything correctly but the value calculated is 0 instead of 24.

Thanks!!

[DonkeyOte]

Given the colours fluctuate within the string you will need to iterate the chars (based on delimiter) in order to determine colour of each value (via Characters).

[drewship]

StephenR, I actually need text to rows then since each serial number must contain the rest of the data in the row. That is actually another thread I thought might help posted here:

http://www.excelforum.com/excel-prog...ml#post2199909

I was trying to modify code you already provided and ran into trouble with letters as part of the serial numbers.

Thanks.

[DonkeyOte]

Based on your earlier upload I suspect the below may work for you ?

Please Login or Register  to view this content.

Called from cell along lines of:

Please Login or Register  to view this content.

[drewship]

DonkeyOte, I looked up Characters in the help. If I understand what it is saying, then I would need something like:

Please Login or Register  to view this content.

The first character in column E row 1 to see if it is black...or would this set the first character to black?

I have discovered that the format provided in the example workbook is preferred so I would rather not have to extract each serial number into its own row since I will have to combine them all again to submit my report. Counting within the cells would be easier in the long run.

Thanks.

[DonkeyOte]

Confused... the code in my prior post does what you want I believe ? (it returns 24 in your sample file)

(on an aside the function can be used to check a range rather than an individual cell, ie if you had data in E1:E10 use E1:E10 as first parameter rather than just E1)

[drewship]

DonkeyOte,...our posts crossed in submission. After I replied to your previous post about using Character, I refreshed the thread and saw your revised code.

I tried it and it seems to work perfectly. I am going to apply it to a copy of the live workbook and I will post my findings.

Thanks!!!

[drewship]

DonkeyOte. I have copied the function to several hundred rows with no problem, but I have uploaded a new workbook with actual cells that are not calculating.

I have tried deleting the data and entering it manually in the same cell but I still get 0 for the calculation. I can't tell what is differrent with the cells that are not working. Any ideas?

Thanks,
Andrew

[DonkeyOte]

In the latter rows the colour has been set as 1 (rather than Auto), ie use 1 rather than -4105 as criteria.

[drewship]

AHHH...so the code for manual black is not the same for automatic black...I wondered where you came up with the -4105...

Thanks!! Will continue to test.

[DonkeyOte]

If you wish only to exclude red it may perhaps be simpler to invert the logic, that is to say pass the colour index of those values to be omitted and alter function accordingly, eg:

Please Login or Register  to view this content.

becomes

Please Login or Register  to view this content.

and cell call becomes

Please Login or Register  to view this content.

ie omit only red (3) ... this way the -4105/1 becomes irrelevant.

[drewship]

Actually, using red as the key for counting will work better now that I have had time to do some testing. Some of the serial numbers are color-coded as to where they are loaned out, but still need to be counted on the parent row. Since red is the only color used to indicate lost items, reversing that little bit of code is perfect. I have already clicked on the scales and will now close this thread with MANY THANKS to another Excel WIZARD!!!!!!

Andrew