Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
BD BD is offline
external usenet poster
 
Posts: 16
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
BD BD is offline
external usenet poster
 
Posts: 16
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,344
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
BD BD is offline
external usenet poster
 
Posts: 16
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
BD BD is offline
external usenet poster
 
Posts: 16
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,344
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
BD BD is offline
external usenet poster
 
Posts: 16
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
BD BD is offline
external usenet poster
 
Posts: 16
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
more than 3 conditional formats per cell nigelb Excel Discussion (Misc queries) 2 December 20th 06 10:48 PM
Why can't I use more than 3 conditional formats per cell? Jillian R Excel Worksheet Functions 1 October 16th 06 06:53 PM
is there any way to have more than 3 conditional formats per cell Greg L Excel Discussion (Misc queries) 8 September 19th 06 12:51 AM
how do I get 8 Conditional Formats for 1 Cell Craig Excel Worksheet Functions 2 December 28th 05 05:42 PM
how do i use multiple conditional formats in one cell? tysonstone Excel Discussion (Misc queries) 1 January 22nd 05 12:15 AM


All times are GMT +1. The time now is 11:42 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"