COUNTIF function help please

[Chris]

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

 

[Patrick Molloy]

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]

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

 

[Patrick Molloy]

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

 

[Patrick Molloy]

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]

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

 

[Chris]

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

 

[Chris]

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

 

[Patrick Molloy]

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