Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT query
"watching too much good ol' boys on TV"
Sounds like your Sunday was better than mine. <g '--- Jim Cone |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct query | Excel Worksheet Functions | |||
SUMPRODUCT query | Excel Discussion (Misc queries) | |||
Another SUMPRODUCT Query | Excel Worksheet Functions | |||
Sumproduct query | Excel Discussion (Misc queries) | |||
I think its a sumproduct query? | Excel Discussion (Misc queries) |