#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default SUMPRODUCT query

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


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 86
Default SUMPRODUCT query

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

On Fri, 15 Jul 2011 23:27:19 +0100, "Rob"
wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,522
Default SUMPRODUCT query

On Jul 15, 6:05*pm, Gord wrote:
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

On Fri, 15 Jul 2011 23:27:19 +0100, "Rob"
wrote:







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.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default SUMPRODUCT query

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





"Don Guillett" wrote in message
...
On Jul 15, 6:05 pm, Gord wrote:
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

On Fri, 15 Jul 2011 23:27:19 +0100, "Rob"
wrote:







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


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


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 86
Default SUMPRODUCT query

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

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

On Sat, 16 Jul 2011 16:50:25 +0100, "Rob"
wrote:

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





"Don Guillett" wrote in message
...
On Jul 15, 6:05 pm, Gord wrote:
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

On Fri, 15 Jul 2011 23:27:19 +0100, "Rob"
wrote:







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


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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default SUMPRODUCT query

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" wrote in message
...
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 InsertModule.

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

On Sat, 16 Jul 2011 16:50:25 +0100, "Rob"
wrote:

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





"Don Guillett" wrote in message
...
On Jul 15, 6:05 pm, Gord wrote:
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

On Fri, 15 Jul 2011 23:27:19 +0100, "Rob"
wrote:







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


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



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 86
Default SUMPRODUCT query

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(xlCellTypeConst ants, 2)
MsgBox WorksheetFunction.CountA(rng) 'either a message box or
Range("F1").Value = WorksheetFunction.CountA(rng) 'a count in a cell
End Sub


Gord


On Sun, 17 Jul 2011 20:55:44 +0100, "Rob"
wrote:

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" wrote in message
.. .
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 InsertModule.

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

On Sat, 16 Jul 2011 16:50:25 +0100, "Rob"
wrote:

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





"Don Guillett" wrote in message
...
On Jul 15, 6:05 pm, Gord wrote:
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

On Fri, 15 Jul 2011 23:27:19 +0100, "Rob"
wrote:







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

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


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,549
Default SUMPRODUCT query

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

=COUNTA(B5:D100)-COUNT(B5:D100)

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"
wrote in message
...
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" wrote in message ...
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 InsertModule.

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

On Sat, 16 Jul 2011 16:50:25 +0100, "Rob"
wrote:

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





"Don Guillett" wrote in message
...
On Jul 15, 6:05 pm, Gord wrote:
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

On Fri, 15 Jul 2011 23:27:19 +0100, "Rob"
wrote:







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

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





  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 86
Default SUMPRODUCT query

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

On Sun, 17 Jul 2011 16:18:37 -0700, "Jim Cone"
wrote:

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

=COUNTA(B5:D100)-COUNT(B5:D100)

It subtracts the number count from the count of all cells with an entry (errors, true/false, text,
numbers, etc).

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,549
Default SUMPRODUCT query

"watching too much good ol' boys on TV"

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




  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default SUMPRODUCT query

Thanks Guys for all your help.

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

Thanks again, Rob



"Gord" wrote in message
...
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

On Sun, 17 Jul 2011 16:18:37 -0700, "Jim Cone"
wrote:

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

=COUNTA(B5:D100)-COUNT(B5:D100)

It subtracts the number count from the count of all cells with an entry
(errors, true/false, text,
numbers, etc).




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
Sumproduct query Marc T Excel Worksheet Functions 4 June 1st 09 04:46 PM
SUMPRODUCT query John Excel Discussion (Misc queries) 3 March 18th 08 11:57 PM
Another SUMPRODUCT Query enna49 Excel Worksheet Functions 4 June 29th 07 06:20 AM
Sumproduct query shakey1181 Excel Discussion (Misc queries) 7 May 18th 07 02:49 PM
I think its a sumproduct query? Scoosh Excel Discussion (Misc queries) 0 September 8th 05 12:45 AM


All times are GMT +1. The time now is 02:56 PM.

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"