SUMPRODUCT query

[Rob]

Hi

I have a table where I want to record any number of items that I pass on to
people by putting their initials into a cell..

Whenever I put the initials of a person into a cell, I want the background
colour for the cell to change to red and then have a "Total" cell which adds
up how many cells have changed colour, ie how many items have I passed on.

JP TC RH BJ SH Total
5



I have used in the past the following
=SUMPRODUCT(--(ColorIndex(B2:G3)=2)) which worked ok for a while but now I
cannot get it to function properly.

Also I have upgraded to Office 2010 so is this why it will not work.

Any help appreciated.

Regards, Rob

 

[Gord]

ColorIndex is not a native Excel function.

I would suggest you had an add-in or personal.xls with that UDF

When you upgraded, you did not bring that with you.

See Chip Pearson's site for some code and a downloadable workbook with
all the colorindex functions.

http://www.cpearson.com/excel/Colors.aspx


Gord Dibben Microsoft Excel MVP

 

[Don Guillett]

ColorIndex is not a native Excel function.

I would suggest you had an add-in or personal.xls with that UDF

When you upgraded, you did not bring that with you.

See Chip Pearson's site for some code and a downloadable workbook with
all the colorindex functions.

http://www.cpearson.com/excel/Colors.aspx

Gord Dibben    Microsoft Excel MVP

Please insert the code below, Example:
=CountColor(A1:C3)
Peter
Place this macro in a REGULAR module. then use the formula
Function CountColor(r As Range) As Long
For Each r In r.Cells
CountColor = CountColor + IIf(r.Font.ColorIndex = 3, 1, 0)
Next
End Function

 

[Rob]

Thanks to both for responding.

However, I am not really sure what you are talking about as I am not that
Excell literate!! It's all a little over my head which, I suppose is why I
posed the question in the first place.

I have looked at Chip Pearsons page but it is mind boggling to me at this
stage as I am just trying to learn a little more. I can see certain things
but I don't know anything about modules, macros or functions.

For example, "how" do I place a macro into a "regular" module. What are
they?? What do I physically have to do to get this to work.

As stated, I am in no way an expert at this, I am just on the first step of
the ladder.

Thanks again

Regards, Rob

ColorIndex is not a native Excel function.

I would suggest you had an add-in or personal.xls with that UDF

When you upgraded, you did not bring that with you.

See Chip Pearson's site for some code and a downloadable workbook with
all the colorindex functions.

http://www.cpearson.com/excel/Colors.aspx

Gord Dibben Microsoft Excel MVP

Please insert the code below, Example:
=CountColor(A1:C3)
Peter
Place this macro in a REGULAR module. then use the formula
Function CountColor(r As Range) As Long
For Each r In r.Cells
CountColor = CountColor + IIf(r.Font.ColorIndex = 3, 1, 0)
Next
End Function

 

[Gord]

First............the cell gets its red color from you manually
formatting that cell?

Or does it get red color due to Conditional Formatting?

Big difference in what code or method is used to count.

From your example formula that used to work I would say that the cells
were manually colored.

Don's function with a modification for background color will work for
these types of cells but not if CF was used for coloring.

Function CountColor(r As Range) As Long
For Each r In r.Cells
CountColor = CountColor + IIf(r.Interior.ColorIndex = 3, 1, 0)
Next
End Function

To install the code in your workbook..........................

With your WB open, hit Alt + F11 to open Visual Basic Editor.

Hit ctrl + r to open Project Explorer.

Expand your WB tree by clicking on the "+" sign.

On Menu above hit Insert>Module.

Paste the code into that module.

Alt + q to return to Excel window.

In cell enter the formula =CountColor(A1:C3) adjust for range.

Note the index of Red is 3, not 2 as in your original SUMPRODUCT
formula.


Gord

 

[Rob]

Thanks Gord

The cells are conditionally formatted so that when I enter text, ie a name
or a place, the background automatically fills with red.

What I am trying to achieve is, on page of several hundred cells, how many
are red, ie how many have text entered into them.

I think the answer below will not work with the CF, right.

Regards Rob

 

[Gord]

Will not work with CF......correct.

Other than text, what else could be in the several hundred cells?

Blanks or numbers which includes dates/times

Are these red cells in a column or row or randomly all over the sheet?

In a single column............You could use a helper column of
=ISTEXT(A1) copied down.

Then in a cell =COUNTIF(B1:B200,TRUE)

Or if randomly located maybe a macro...............

Sub counttext()
Dim rng As Range
Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, 2)
MsgBox WorksheetFunction.CountA(rng) 'either a message box or
Range("F1").Value = WorksheetFunction.CountA(rng) 'a count in a cell
End Sub


Gord

 

[Jim Cone]

To just get the count of cells that have text, you can use...

=COUNTA(B5100)-COUNT(B5100)

It subtracts the number count from the count of all cells with an entry (errors, true/false, text,
numbers, etc).
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)




"Rob" <[email protected]>
wrote in message

 

[Gord]

Thanks Jim

That's the one I was trying to think of<g>

Brain dead Sunday............watching too much good ol' boys on TV


Gord

 

[Jim Cone]

Re: "watching too much good ol' boys on TV"

Sounds like your Sunday was better than mine. <g>
'---
Jim Cone

 

[Rob]

Thanks Guys for all your help.

Jim, this worked fine and is all that I was looking for
COUNTA(B5100)-COUNT(B5100)

Thanks again, Rob