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 ***

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 ***

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 ***

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 ***

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 ***

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 ***

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 ***

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 ***

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 ***

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 ***