If I have a column of numbers and some of the cells are highlighted
(show in bold) eg.
A

1 7
2 *5*
3 6
4 8
5 10
6 *15*

Sub Total 1
Sub Total 2


How can I get sub total 1 to add up all the non highlighted numbers in
the column and sub total 2 to add up all the highlighted numbers?

The numbers dont necessarily have to be highlighted, they could be in
bold or a different colour.

Also bear in mind that it doesnt have to be cell A6 or A2 that is
highlighted it can be any particular cell in the column.

I know I can make sub total 1 = A1+A3+A4+A5
and sub total 2 = A2+A6

but when I've got a lot of data I dont really want to have to keep
typing in the functions manually depending on where the highlighted
cells are.

Hope this all makes sense and thanks for any help


--
frustrated
------------------------------------------------------------------------
frustrated's Profile: http://www.excelforum.com/member.php...o&userid=27778
View this thread: http://www.excelforum.com/showthread...hreadid=493442

See http://www.xldynamic.com/source/xld.ColourCounter.html for a technique

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"frustrated" wrote
in message ...

If I have a column of numbers and some of the cells are highlighted
(show in bold) eg.
A

1 7
2 *5*
3 6
4 8
5 10
6 *15*

Sub Total 1
Sub Total 2


How can I get sub total 1 to add up all the non highlighted numbers in
the column and sub total 2 to add up all the highlighted numbers?

The numbers dont necessarily have to be highlighted, they could be in
bold or a different colour.

Also bear in mind that it doesnt have to be cell A6 or A2 that is
highlighted it can be any particular cell in the column.

I know I can make sub total 1 = A1+A3+A4+A5
and sub total 2 = A2+A6

but when I've got a lot of data I dont really want to have to keep
typing in the functions manually depending on where the highlighted
cells are.

Hope this all makes sense and thanks for any help


--
frustrated
------------------------------------------------------------------------
frustrated's Profile:
http://www.excelforum.com/member.php...o&userid=27778
View this thread: http://www.excelforum.com/showthread...hreadid=493442

Thanks for this info. But I cant get it to work.

I am using the function SUMPRODUCT(--(ColorIndex(A1:A100)=3),A1:A100)

Do I need to substitute a number for --

Also does this work if cells in the column have a few different colours


--
frustrated
------------------------------------------------------------------------
frustrated's Profile: http://www.excelforum.com/member.php...o&userid=27778
View this thread: http://www.excelforum.com/showthread...hreadid=493442

Here is a screenshot of my worksheet.

By the way the colours are an integral part of the worksheet and I cant
get rid of them.


+-------------------------------------------------------------------+
|Filename: example.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=4125 |
+-------------------------------------------------------------------+

--
frustrated
------------------------------------------------------------------------
frustrated's Profile: http://www.excelforum.com/member.php...o&userid=27778
View this thread: http://www.excelforum.com/showthread...hreadid=493442

No, you don't need to change the --, that is meant to be there as is.

But ...

your data in A1:An looks like text to me, so you cannot sum that.
And also colorindex = 3 is red, your cells are not that colour to me, they
look like green and two shades of orange
it doesn't work on conditionally formatted cells.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"frustrated" wrote
in message ...

Thanks for this info. But I cant get it to work.

I am using the function SUMPRODUCT(--(ColorIndex(A1:A100)=3),A1:A100)

Do I need to substitute a number for --

Also does this work if cells in the column have a few different colours


--
frustrated
------------------------------------------------------------------------
frustrated's Profile:
http://www.excelforum.com/member.php...o&userid=27778
View this thread: http://www.excelforum.com/showthread...hreadid=493442

Is there is any rhyme or reason behind which cells are highlighted & which
aren't? Conditional Formatting, for instance. If so, use the SUMIF()
function and incorporate the same condition you used in the Conditional
Formatting

If it's something else, see Chip Pearson's site:

http://www.cpearson.com/excel/SortByColor.htm


"frustrated" wrote:


If I have a column of numbers and some of the cells are highlighted
(show in bold) eg.
A

1 7
2 *5*
3 6
4 8
5 10
6 *15*

Sub Total 1
Sub Total 2


How can I get sub total 1 to add up all the non highlighted numbers in
the column and sub total 2 to add up all the highlighted numbers?

The numbers dont necessarily have to be highlighted, they could be in
bold or a different colour.

Also bear in mind that it doesnt have to be cell A6 or A2 that is
highlighted it can be any particular cell in the column.

I know I can make sub total 1 = A1+A3+A4+A5
and sub total 2 = A2+A6

but when I've got a lot of data I dont really want to have to keep
typing in the functions manually depending on where the highlighted
cells are.

Hope this all makes sense and thanks for any help


--
frustrated
------------------------------------------------------------------------
frustrated's Profile: http://www.excelforum.com/member.php...o&userid=27778
View this thread: http://www.excelforum.com/showthread...hreadid=493442

If you need 2 or more criteria in your sum, use a formula like

=SUMPRODUCT(--(A1:A100="debit"),--(B1:B100<=DATE(2005,5,31)),C1:C100)

which adds all the values in column C that are labeled in cal A as debits
and dated in col B as May 31 or earlier



"Louise" wrote:

Further to my previous post, I've just worked it out:

=SUMIF(A7:A20,"100",A7:A20)

This would only add those figures which are greater than 100.

That's great.

Thank you.

"Duke Carey" wrote:

Is there is any rhyme or reason behind which cells are highlighted & which
aren't? Conditional Formatting, for instance. If so, use the SUMIF()
function and incorporate the same condition you used in the Conditional
Formatting

If it's something else, see Chip Pearson's site:

http://www.cpearson.com/excel/SortByColor.htm


"frustrated" wrote:


If I have a column of numbers and some of the cells are highlighted
(show in bold) eg.
A

1 7
2 *5*
3 6
4 8
5 10
6 *15*

Sub Total 1
Sub Total 2


How can I get sub total 1 to add up all the non highlighted numbers in
the column and sub total 2 to add up all the highlighted numbers?

The numbers dont necessarily have to be highlighted, they could be in
bold or a different colour.

Also bear in mind that it doesnt have to be cell A6 or A2 that is
highlighted it can be any particular cell in the column.

I know I can make sub total 1 = A1+A3+A4+A5
and sub total 2 = A2+A6

but when I've got a lot of data I dont really want to have to keep
typing in the functions manually depending on where the highlighted
cells are.

Hope this all makes sense and thanks for any help


--
frustrated
------------------------------------------------------------------------
frustrated's Profile: http://www.excelforum.com/member.php...o&userid=27778
View this thread: http://www.excelforum.com/showthread...hreadid=493442

If the cells were formatted due to applying Conditional Formatting, eg.
anything greater than 100 to be formatted in bold, how would you create the
SUMIF function to incorporate that? I have never used that before but it
would be useful for me to know.

Thank you.

Louise

"Duke Carey" wrote:

Is there is any rhyme or reason behind which cells are highlighted & which
aren't? Conditional Formatting, for instance. If so, use the SUMIF()
function and incorporate the same condition you used in the Conditional
Formatting

If it's something else, see Chip Pearson's site:

http://www.cpearson.com/excel/SortByColor.htm


"frustrated" wrote:


If I have a column of numbers and some of the cells are highlighted
(show in bold) eg.
A

1 7
2 *5*
3 6
4 8
5 10
6 *15*

Sub Total 1
Sub Total 2


How can I get sub total 1 to add up all the non highlighted numbers in
the column and sub total 2 to add up all the highlighted numbers?

The numbers dont necessarily have to be highlighted, they could be in
bold or a different colour.

Also bear in mind that it doesnt have to be cell A6 or A2 that is
highlighted it can be any particular cell in the column.

I know I can make sub total 1 = A1+A3+A4+A5
and sub total 2 = A2+A6

but when I've got a lot of data I dont really want to have to keep
typing in the functions manually depending on where the highlighted
cells are.

Hope this all makes sense and thanks for any help


--
frustrated
------------------------------------------------------------------------
frustrated's Profile: http://www.excelforum.com/member.php...o&userid=27778
View this thread: http://www.excelforum.com/showthread...hreadid=493442

Umm I'm confused.


There is a reason that a particular cell is highlighted. Highlighted
cells represent a day that an order is cancelled. I dont want to do
anything to the date column.

What I want is this:

Have a cell that ignores all of column A's highlighed values (in bright
orange) and adds up the rest

A second cell that ignores all *non highlighted* values and only adds
up highlighted values.

Take a look at the following picture to see what I mean

Thanks


+-------------------------------------------------------------------+
|Filename: sample 2.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=4126 |
+-------------------------------------------------------------------+

--
frustrated
------------------------------------------------------------------------
frustrated's Profile: http://www.excelforum.com/member.php...o&userid=27778
View this thread: http://www.excelforum.com/showthread...hreadid=493442

Further to my previous post, I've just worked it out:

=SUMIF(A7:A20,"100",A7:A20)

This would only add those figures which are greater than 100.

That's great.

Thank you.

"Duke Carey" wrote:

Is there is any rhyme or reason behind which cells are highlighted & which
aren't? Conditional Formatting, for instance. If so, use the SUMIF()
function and incorporate the same condition you used in the Conditional
Formatting

If it's something else, see Chip Pearson's site:

http://www.cpearson.com/excel/SortByColor.htm


"frustrated" wrote:


If I have a column of numbers and some of the cells are highlighted
(show in bold) eg.
A

1 7
2 *5*
3 6
4 8
5 10
6 *15*

Sub Total 1
Sub Total 2


How can I get sub total 1 to add up all the non highlighted numbers in
the column and sub total 2 to add up all the highlighted numbers?

The numbers dont necessarily have to be highlighted, they could be in
bold or a different colour.

Also bear in mind that it doesnt have to be cell A6 or A2 that is
highlighted it can be any particular cell in the column.

I know I can make sub total 1 = A1+A3+A4+A5
and sub total 2 = A2+A6

but when I've got a lot of data I dont really want to have to keep
typing in the functions manually depending on where the highlighted
cells are.

Hope this all makes sense and thanks for any help


--
frustrated
------------------------------------------------------------------------
frustrated's Profile: http://www.excelforum.com/member.php...o&userid=27778
View this thread: http://www.excelforum.com/showthread...hreadid=493442

See my reply. You need to do some work to translate the info given to your
circumstances, we are not a bureau.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"frustrated" wrote
in message ...

Umm I'm confused.


There is a reason that a particular cell is highlighted. Highlighted
cells represent a day that an order is cancelled. I dont want to do
anything to the date column.

What I want is this:

Have a cell that ignores all of column A's highlighed values (in bright
orange) and adds up the rest

A second cell that ignores all *non highlighted* values and only adds
up highlighted values.

Take a look at the following picture to see what I mean

Thanks


+-------------------------------------------------------------------+
|Filename: sample 2.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=4126 |
+-------------------------------------------------------------------+

--
frustrated
------------------------------------------------------------------------
frustrated's Profile:
http://www.excelforum.com/member.php...o&userid=27778
View this thread: http://www.excelforum.com/showthread...hreadid=493442

Did you go to Chip's site and copy the cellcolorindex Function and paste it to
a general module in your workbook?

If so, you must use it to find the index numbers of the colored cells.

Then you can SUM up on those index numbers.

In B1 enter =CellColorIndex(A1) Drag/copy that down as far as you need.

Non-colored cells will return -4142

Colored cells will return another number.

Now in C1 enter =SUMIF(B1:B100,-4142,A1:A100)

Same in D1 for any other number.

Note: if the cells were colored due to Conditional Formatting, none of the
above will work.


Gord Dibben Excel MVP


"frustrated" wrote
in message ...

Umm I'm confused.


There is a reason that a particular cell is highlighted. Highlighted
cells represent a day that an order is cancelled. I dont want to do
anything to the date column.

What I want is this:

Have a cell that ignores all of column A's highlighed values (in bright
orange) and adds up the rest

A second cell that ignores all *non highlighted* values and only adds
up highlighted values.

Take a look at the following picture to see what I mean

Thanks

Ok I have managed to find the index numbers of the coloured cells.

They are as follows:

Orange 40
Light Yellow 19
Purple 24
Light Blue 20
Grey 15
Bright Yellow 44

Now I'm having trouble actually applying the sum function.

I get stuck from this point onwards:

In B1 enter =CellColorIndex(A1)

Why am I doing this and what does this mean??

Also what is conditonal formatting and how do I know if I have applied
this?

Sorry for not getting it - I'm quite slow when it comes to programming

Thanks for the help


--
frustrated
------------------------------------------------------------------------
frustrated's Profile: http://www.excelforum.com/member.php...o&userid=27778
View this thread: http://www.excelforum.com/showthread...hreadid=493442

I have made a sample worksheet so that you can see the end result i'm
hoping for. The bright yellow line (colour index 44) is the
"highlighted line" I keep referring to and it represents days that an
order was sent elsewhere.

Click for image: http://img4.imageshack.us/img4/1030/sample39sh.jpg


Hope this helps


--
frustrated
------------------------------------------------------------------------
frustrated's Profile: http://www.excelforum.com/member.php...o&userid=27778
View this thread: http://www.excelforum.com/showthread...hreadid=493442

Have edited my post so it is clear exactly what I am trying to do. I was
working late last night so I wasn't making much sense!

Thanks for all your help


--
frustrated
------------------------------------------------------------------------
frustrated's Profile: http://www.excelforum.com/member.php...o&userid=27778
View this thread: http://www.excelforum.com/showthread...hreadid=493442