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 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

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

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