Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
COUNTIF function help please
Hi, could someone please help me with the following?
I have the following: 1. Worksheet named: Summary has a dropdown list in cell B2 for a list of Operations. These Operations are detailed on Worksheet named: Operations. The defined name for these Operations = Operations!$A$2:$A$12. 2. Worksheet named: Summary has a dropdown list in cell B4 for a list of Groups. These Groups are detailed on worksheet named: Groups. The defined name for these Groups = Groups!$A$2:$A$29. What I need is a macro or code to do the following after the user has selected both an Operation and a Group: I need it to lookup column E for all the cells that contain the text: "PERMANENT" or "FUTURE" or "TEMPORARY" or "FUTURE DELETION" from the worksheet named: Data. I wanted to use the COUNTIF function to count how many occurrences of these four texts. For example: =COUNTIF($E$3:$E$65000,"PERMANENT"). in cell B8 (on worksheet named Summary) - I need the COUNTIF function results for "PERMANENT". in cell B9 (on worksheet named Summary) - I need the COUNTIF function results for "FUTURE". in cell B10 (on worksheet named Summary) - I need the COUNTIF function results for "TEMPORARY". in cell B11 (on worksheet named Summary) - I need the COUNTIF function results for "FUTURE DELETION". I also need it to lookup column U for all the cells that contain the text: "Y" from the worksheet named: Data. I wanted to use the COUNTIF function to count how many occurrences of these four texts. For example: =COUNTIF($U$3:$U$65000,"Y"). in cell B25 (on worksheet named Summary) - I need the COUNTIF function results for "Y". If anyone could please help with this, it would be greatly appreciated. Kind regards, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
COUNTIF function help please
it looks like you already have the formula....you give four of them as your
examples....so I'm not clear what your question is. Perhaps you could rephrase for me? "Chris" wrote in message ... Hi, could someone please help me with the following? I have the following: 1. Worksheet named: Summary has a dropdown list in cell B2 for a list of Operations. These Operations are detailed on Worksheet named: Operations. The defined name for these Operations = Operations!$A$2:$A$12. 2. Worksheet named: Summary has a dropdown list in cell B4 for a list of Groups. These Groups are detailed on worksheet named: Groups. The defined name for these Groups = Groups!$A$2:$A$29. What I need is a macro or code to do the following after the user has selected both an Operation and a Group: I need it to lookup column E for all the cells that contain the text: "PERMANENT" or "FUTURE" or "TEMPORARY" or "FUTURE DELETION" from the worksheet named: Data. I wanted to use the COUNTIF function to count how many occurrences of these four texts. For example: =COUNTIF($E$3:$E$65000,"PERMANENT"). in cell B8 (on worksheet named Summary) - I need the COUNTIF function results for "PERMANENT". in cell B9 (on worksheet named Summary) - I need the COUNTIF function results for "FUTURE". in cell B10 (on worksheet named Summary) - I need the COUNTIF function results for "TEMPORARY". in cell B11 (on worksheet named Summary) - I need the COUNTIF function results for "FUTURE DELETION". I also need it to lookup column U for all the cells that contain the text: "Y" from the worksheet named: Data. I wanted to use the COUNTIF function to count how many occurrences of these four texts. For example: =COUNTIF($U$3:$U$65000,"Y"). in cell B25 (on worksheet named Summary) - I need the COUNTIF function results for "Y". If anyone could please help with this, it would be greatly appreciated. Kind regards, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
COUNTIF function help please
Hi Patrick, Sorry I did not make this very clear. You are right, I do
have the formula but do not know how to put it all together to make this work. For example: when the user clicks on the Summary tab and then clicks on cell B2 to select an Operation, and then clicks on cell B4 to select a Group, I need some code or macro that will automatically populate cells: B8:B11 & B25. This populated data needs to be extracted from the worksheet named: Data. I wanted to use the COUNTIF function to extract the values from the worksheet named: Data into the cells B8:B11 & B25 on worksheet named: Summary. The Operations are populated on worksheet named: Data in column B. I made a list of these operations on worksheet named: Operations and gave this list a defined name = Operations!$A$2:$A$12. This was done so that I could let the user click on drop-down lists in column B (on worksheet named: Data) to select an appropriate Operation. The Groups are populated on worksheet named: Data in column C. I made a list of these groups on worksheet named: Groups and gave this list a defined name = Groups!$A$2:$A$29. This was done so that I could let the user click on drop-down lists in column C (on worksheet named: Data) to select an appropriate Group. I hope this is clearer - if not please let me know. I really appreciate any help that I can get. Cheers, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
COUNTIF function help please
so basically all you really need is to copy the automatic filter off your
data sheet where the two mentioned filters are set by the two drop downs "Chris" wrote in message ... Hi Patrick, Sorry I did not make this very clear. You are right, I do have the formula but do not know how to put it all together to make this work. For example: when the user clicks on the Summary tab and then clicks on cell B2 to select an Operation, and then clicks on cell B4 to select a Group, I need some code or macro that will automatically populate cells: B8:B11 & B25. This populated data needs to be extracted from the worksheet named: Data. I wanted to use the COUNTIF function to extract the values from the worksheet named: Data into the cells B8:B11 & B25 on worksheet named: Summary. The Operations are populated on worksheet named: Data in column B. I made a list of these operations on worksheet named: Operations and gave this list a defined name = Operations!$A$2:$A$12. This was done so that I could let the user click on drop-down lists in column B (on worksheet named: Data) to select an appropriate Operation. The Groups are populated on worksheet named: Data in column C. I made a list of these groups on worksheet named: Groups and gave this list a defined name = Groups!$A$2:$A$29. This was done so that I could let the user click on drop-down lists in column C (on worksheet named: Data) to select an appropriate Group. I hope this is clearer - if not please let me know. I really appreciate any help that I can get. Cheers, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
COUNTIF function help please
something like this maybe?
Sub GetData() Dim index As Long Dim rw As Range With Worksheets("Summary") .Range("B8:B11").ClearContent For Each rw In Worksheets("S=data").Range("Data") If rw.Range("B1") = .Range("B4") And _ rw.Range("C1") = .Range("B8") Then .Range("B8").Offset(index) = rw.Range("A1").Value index = index +1 End If Next End With End Sub so this looks at each row in the data sheet and where the value in column B matches the value in B4 and where the value in column C matches the value in B8 then the value in column A is copies to the summary sheet. "Chris" wrote in message ... Hi Patrick, Sorry I did not make this very clear. You are right, I do have the formula but do not know how to put it all together to make this work. For example: when the user clicks on the Summary tab and then clicks on cell B2 to select an Operation, and then clicks on cell B4 to select a Group, I need some code or macro that will automatically populate cells: B8:B11 & B25. This populated data needs to be extracted from the worksheet named: Data. I wanted to use the COUNTIF function to extract the values from the worksheet named: Data into the cells B8:B11 & B25 on worksheet named: Summary. The Operations are populated on worksheet named: Data in column B. I made a list of these operations on worksheet named: Operations and gave this list a defined name = Operations!$A$2:$A$12. This was done so that I could let the user click on drop-down lists in column B (on worksheet named: Data) to select an appropriate Operation. The Groups are populated on worksheet named: Data in column C. I made a list of these groups on worksheet named: Groups and gave this list a defined name = Groups!$A$2:$A$29. This was done so that I could let the user click on drop-down lists in column C (on worksheet named: Data) to select an appropriate Group. I hope this is clearer - if not please let me know. I really appreciate any help that I can get. Cheers, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
COUNTIF function help please
Hi Patrick,
I am trying to let the user select via drop-down lists (on worksheet named: Summary) the required Operation and Group. The actual Operations are populated on the worksheet named: Data in column B. The actual Groups are populated on worksheet named: Data in column C. So when the user clicks on the Summary tab (worksheet named: Summary) and then clicks on cell B2 to select an Operation (from a drop-down list), and then clicks on cell B4 to select a Group (from a drop-down list), I need some code or macro that will automatically populate cells: B8:B11 & B25 (on worksheet named: Summary). This populated data needs to be extracted from the worksheet named: Data. I wanted to use the COUNTIF function to extract the values from the worksheet named: Data into the cells B8:B11 & B25 on worksheet named: Summary. I simply do not know how to code this so that when the user selects the worksheet named: Summary and selects the appropriate Operation from the drop-down list in cell B2 and then selects the appropriate Group from the drop-down list in cell B4 then to apply code or macro so that the two selected criteria (Operation and Group) are looked up on the worksheet named: Data to return the COUNTIF values from the worksheet named: Data for example =COUNTIF($E$3:$E$65000,"PERMANENT"), =COUNTIF($E$3:$E$65000,"FUTURE"), =COUNTIF($E$3:$E$65000,"TEMPORARY"), =COUNTIF($E$3:$E$65000,"FUTURE DELETION"), = COUNTIF($U$3:$U$65000,"Y"). I need these five COUNTIF values to be populated into cells B8:b11 & B25 on worksheet named: Summary. kind regards, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
COUNTIF function help please
Hi Patrick, thanks for your code, but I keep getting a run time error
438 at line: .Range("B8:B11").ClearContent. I am not sure if your code will do what I need it to do. Please refer to my previous post. It is basically a two-way lookup (looking up both Operations in column B and Groups in column C in worksheet named: Data and then performing the COUNTIF calculations on columns E and U on the worksheet named: Data. It then returns these calculations to the worksheet named: Summary in cells B8:B11 & B25 as described earlier. I am just using drop-down lists in worksheet named: Summary to enable the user to select the desired Operation and Group in cells B2 and B4 accordingly. Kind regards, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
COUNTIF function help please
Hi Patrick, would it be possible to send you a copy of my workbook to
have a look at? If so, could you please advise of your e-mail address? Kind regards, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
COUNTIF function help please
missed this.
yes of course , but i;m out of my office quite a bit in th enext coupl eof days ... golf and dentist :) :( patrick_molloy AT hotmail DOT com "Chris" wrote in message ... Hi Patrick, would it be possible to send you a copy of my workbook to have a look at? If so, could you please advise of your e-mail address? Kind regards, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
COUNTIF function help please
missed this.
yes of course , but i;m out of my office quite a bit in th enext coupl eof days ... golf and dentist :) :( patrick_molloy AT hotmail DOT com "Chris" wrote in message ... Hi Patrick, would it be possible to send you a copy of my workbook to have a look at? If so, could you please advise of your e-mail address? Kind regards, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF Function | Excel Worksheet Functions | |||
please help how to combine IF function with Countif function | Excel Worksheet Functions | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
Embed a countif function in subtotal function? | Excel Worksheet Functions | |||
IF Function or Countif | Excel Worksheet Functions |