Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formula in terms of cell formats
I have a sheet with some numbers in white and some in black. I can use the
"find" function to isolate and find the white numbers and then manually add all of them up separately. How could I directly write a formula in a cell to sum up all the white numbers? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formula in terms of cell formats
Write a formula based on the logic of *why* the numbers are white.
-- Biff Microsoft Excel MVP "bd" wrote in message ... I have a sheet with some numbers in white and some in black. I can use the "find" function to isolate and find the white numbers and then manually add all of them up separately. How could I directly write a formula in a cell to sum up all the white numbers? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formula in terms of cell formats
They are manual inputs, but they are white because they are first in a
string, meaning physically on the sheet, (1) there is a blank cell to its left and a blank cell to its right or (2) a blank cell to its left and the same number in black to its right. Is there a formula based on this logic? thanks. "T. Valko" wrote: Write a formula based on the logic of *why* the numbers are white. -- Biff Microsoft Excel MVP "bd" wrote in message ... I have a sheet with some numbers in white and some in black. I can use the "find" function to isolate and find the white numbers and then manually add all of them up separately. How could I directly write a formula in a cell to sum up all the white numbers? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formula in terms of cell formats
Hi,
If you don't get a good answer for this by tomorrow, reply to my post if you want a VBA solution. You can create a custom function the sums based on font color. I'm too tired to work on it tonight. 12:00 here! -- Thanks, Shane Devenshire "bd" wrote: I have a sheet with some numbers in white and some in black. I can use the "find" function to isolate and find the white numbers and then manually add all of them up separately. How could I directly write a formula in a cell to sum up all the white numbers? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formula in terms of cell formats
Hi,
This is not a perfect solution in that you cannot get a result in a cell. However, you can use the following trick to read the result in the lower right corner of Excel: 1. Press Ctrl+F; 2. Click on Options and in the down arrow on the format button, select Choose format from cell 3. Now select the cell which has the white colour 4. Click on Find All and then do Ctrl+A; 5. when you click on Close, you will notice that all white numbers will be highlighted; 6. You will be able to see the sum in the lower right corner. if you see any other function there such as Average, please right click there and select Sum Hope this helps -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "bd" wrote in message ... I have a sheet with some numbers in white and some in black. I can use the "find" function to isolate and find the white numbers and then manually add all of them up separately. How could I directly write a formula in a cell to sum up all the white numbers? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formula in terms of cell formats
Thank you Ashish. I'll use this solution if the custom VBA solution doesn't
come through. "Ashish Mathur" wrote: Hi, This is not a perfect solution in that you cannot get a result in a cell. However, you can use the following trick to read the result in the lower right corner of Excel: 1. Press Ctrl+F; 2. Click on Options and in the down arrow on the format button, select Choose format from cell 3. Now select the cell which has the white colour 4. Click on Find All and then do Ctrl+A; 5. when you click on Close, you will notice that all white numbers will be highlighted; 6. You will be able to see the sum in the lower right corner. if you see any other function there such as Average, please right click there and select Sum Hope this helps -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "bd" wrote in message ... I have a sheet with some numbers in white and some in black. I can use the "find" function to isolate and find the white numbers and then manually add all of them up separately. How could I directly write a formula in a cell to sum up all the white numbers? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formula in terms of cell formats
The condition for cell B1 could be:
=AND(ISBLANK(A1),NOT(ISBLANK(B1)),OR(ISBLANK(C1),C 1=B1)) but of course that would not exclude a case where C1 had the same value as B1 but again in white. If you need to distinguish those, then you'd need a VBA solution, so look at http://www.cpearson.com/excel/colors.aspx or http://xldynamic.com/source/xld.ColourCounter.html. -- David Biddulph "bd" wrote in message ... They are manual inputs, but they are white because they are first in a string, meaning physically on the sheet, (1) there is a blank cell to its left and a blank cell to its right or (2) a blank cell to its left and the same number in black to its right. Is there a formula based on this logic? thanks. "T. Valko" wrote: Write a formula based on the logic of *why* the numbers are white. -- Biff Microsoft Excel MVP "bd" wrote in message ... I have a sheet with some numbers in white and some in black. I can use the "find" function to isolate and find the white numbers and then manually add all of them up separately. How could I directly write a formula in a cell to sum up all the white numbers? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formula in terms of cell formats
Yes please Shane. Please help with a VBA solution.
regards, cheryl "ShaneDevenshire" wrote: Hi, If you don't get a good answer for this by tomorrow, reply to my post if you want a VBA solution. You can create a custom function the sums based on font color. I'm too tired to work on it tonight. 12:00 here! -- Thanks, Shane Devenshire "bd" wrote: I have a sheet with some numbers in white and some in black. I can use the "find" function to isolate and find the white numbers and then manually add all of them up separately. How could I directly write a formula in a cell to sum up all the white numbers? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formula in terms of cell formats
Hi,
Here is some code which should do what you want: Function CountFormats(R As Range, E As Range) As Integer Dim cell As Range Dim Total As Integer Dim T As Boolean Set S = E.Cells(1, 1) Total = 0 For Each cell In R T = True With cell If .Font.ColorIndex < S.Font.ColorIndex Then T = False End With If T = True Then Total = Total + 1 End If Next cell CountFormats = Total End Function Here is how you use it enter =CountFormats(A1:F13,D5) In a cell. A1:F13 indicates the range you want to check, cell D5 or any cell that is formatted with the font color you want to count. This way you don't need to guess what the number of the font color is, you just specify a cell that has that font color and the function does the rest. If this helps, please click the Yes button. -- Thanks, Shane Devenshire "bd" wrote: Thank you Ashish. I'll use this solution if the custom VBA solution doesn't come through. "Ashish Mathur" wrote: Hi, This is not a perfect solution in that you cannot get a result in a cell. However, you can use the following trick to read the result in the lower right corner of Excel: 1. Press Ctrl+F; 2. Click on Options and in the down arrow on the format button, select Choose format from cell 3. Now select the cell which has the white colour 4. Click on Find All and then do Ctrl+A; 5. when you click on Close, you will notice that all white numbers will be highlighted; 6. You will be able to see the sum in the lower right corner. if you see any other function there such as Average, please right click there and select Sum Hope this helps -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "bd" wrote in message ... I have a sheet with some numbers in white and some in black. I can use the "find" function to isolate and find the white numbers and then manually add all of them up separately. How could I directly write a formula in a cell to sum up all the white numbers? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formula in terms of cell formats
You're welcome
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "bd" wrote in message ... Thank you Ashish. I'll use this solution if the custom VBA solution doesn't come through. "Ashish Mathur" wrote: Hi, This is not a perfect solution in that you cannot get a result in a cell. However, you can use the following trick to read the result in the lower right corner of Excel: 1. Press Ctrl+F; 2. Click on Options and in the down arrow on the format button, select Choose format from cell 3. Now select the cell which has the white colour 4. Click on Find All and then do Ctrl+A; 5. when you click on Close, you will notice that all white numbers will be highlighted; 6. You will be able to see the sum in the lower right corner. if you see any other function there such as Average, please right click there and select Sum Hope this helps -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "bd" wrote in message ... I have a sheet with some numbers in white and some in black. I can use the "find" function to isolate and find the white numbers and then manually add all of them up separately. How could I directly write a formula in a cell to sum up all the white numbers? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formula in terms of cell formats
Sorry I'm a rookie in this. I copied everything in the visual basic editor
and it gave me a #NAME? error when I use it on the sheet. "ShaneDevenshire" wrote: Hi, Here is some code which should do what you want: Function CountFormats(R As Range, E As Range) As Integer Dim cell As Range Dim Total As Integer Dim T As Boolean Set S = E.Cells(1, 1) Total = 0 For Each cell In R T = True With cell If .Font.ColorIndex < S.Font.ColorIndex Then T = False End With If T = True Then Total = Total + 1 End If Next cell CountFormats = Total End Function Here is how you use it enter =CountFormats(A1:F13,D5) In a cell. A1:F13 indicates the range you want to check, cell D5 or any cell that is formatted with the font color you want to count. This way you don't need to guess what the number of the font color is, you just specify a cell that has that font color and the function does the rest. If this helps, please click the Yes button. -- Thanks, Shane Devenshire "bd" wrote: Thank you Ashish. I'll use this solution if the custom VBA solution doesn't come through. "Ashish Mathur" wrote: Hi, This is not a perfect solution in that you cannot get a result in a cell. However, you can use the following trick to read the result in the lower right corner of Excel: 1. Press Ctrl+F; 2. Click on Options and in the down arrow on the format button, select Choose format from cell 3. Now select the cell which has the white colour 4. Click on Find All and then do Ctrl+A; 5. when you click on Close, you will notice that all white numbers will be highlighted; 6. You will be able to see the sum in the lower right corner. if you see any other function there such as Average, please right click there and select Sum Hope this helps -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "bd" wrote in message ... I have a sheet with some numbers in white and some in black. I can use the "find" function to isolate and find the white numbers and then manually add all of them up separately. How could I directly write a formula in a cell to sum up all the white numbers? |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formula in terms of cell formats
And I wasn't trying to count the number of cells, I was trying to add up (sum
up) the numbers that are in white. Your code looks like it's counting the cells, if I am not mistaken... "bd" wrote: Sorry I'm a rookie in this. I copied everything in the visual basic editor and it gave me a #NAME? error when I use it on the sheet. "ShaneDevenshire" wrote: Hi, Here is some code which should do what you want: Function CountFormats(R As Range, E As Range) As Integer Dim cell As Range Dim Total As Integer Dim T As Boolean Set S = E.Cells(1, 1) Total = 0 For Each cell In R T = True With cell If .Font.ColorIndex < S.Font.ColorIndex Then T = False End With If T = True Then Total = Total + 1 End If Next cell CountFormats = Total End Function Here is how you use it enter =CountFormats(A1:F13,D5) In a cell. A1:F13 indicates the range you want to check, cell D5 or any cell that is formatted with the font color you want to count. This way you don't need to guess what the number of the font color is, you just specify a cell that has that font color and the function does the rest. If this helps, please click the Yes button. -- Thanks, Shane Devenshire "bd" wrote: Thank you Ashish. I'll use this solution if the custom VBA solution doesn't come through. "Ashish Mathur" wrote: Hi, This is not a perfect solution in that you cannot get a result in a cell. However, you can use the following trick to read the result in the lower right corner of Excel: 1. Press Ctrl+F; 2. Click on Options and in the down arrow on the format button, select Choose format from cell 3. Now select the cell which has the white colour 4. Click on Find All and then do Ctrl+A; 5. when you click on Close, you will notice that all white numbers will be highlighted; 6. You will be able to see the sum in the lower right corner. if you see any other function there such as Average, please right click there and select Sum Hope this helps -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "bd" wrote in message ... I have a sheet with some numbers in white and some in black. I can use the "find" function to isolate and find the white numbers and then manually add all of them up separately. How could I directly write a formula in a cell to sum up all the white numbers? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
more than 3 conditional formats per cell | Excel Discussion (Misc queries) | |||
Why can't I use more than 3 conditional formats per cell? | Excel Worksheet Functions | |||
is there any way to have more than 3 conditional formats per cell | Excel Discussion (Misc queries) | |||
how do I get 8 Conditional Formats for 1 Cell | Excel Worksheet Functions | |||
how do i use multiple conditional formats in one cell? | Excel Discussion (Misc queries) |