Ignoring highlighted cells

  • Thread starter Thread starter frustrated
  • Start date Start date
F

frustrated

If I have a column of numbers and some of the cells are highlighte
(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 i
the column and sub total 2 to add up all the highlighted numbers?

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

Also bear in mind that it doesnt have to be cell A6 or A2 that i
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 kee
typing in the functions manually depending on where the highlighte
cells are.

Hope this all makes sense and thanks for any hel
 
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
 
Here is a screenshot of my worksheet.

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

+-------------------------------------------------------------------
|Filename: example.JPG
|Download: http://www.excelforum.com/attachment.php?postid=4125
+-------------------------------------------------------------------
 
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)
 
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
 
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
 
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
 
Umm I'm confused.


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

What I want is this:

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

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

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

Thank

+-------------------------------------------------------------------
|Filename: sample 2.JPG
|Download: http://www.excelforum.com/attachment.php?postid=4126
+-------------------------------------------------------------------
 
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.
 
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)
 
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
 
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)
Click to expand...

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

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

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

Thanks for the hel
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Total a column (or row0 by highlighting cells 1
COUNT function 2
row and cell highlighted 2
JUMP FROM "SEARCH" CELL TO RESULT WHEN PRESSING ENTER 1
Excel JUMP FROM SEARCH BAR TO RESULT 3
highlighting duplicate values 2
Excel Staff Grade and Shift 0
Average Question 2

Back
Top