COUNTIF calculations & Two-way Lookup

[Chris Hankin]

Hi, could someone please help with the following? I need to undertake a
COUNTIF calculations and a two-way lookup.

I need the user to select an Operation and Task Group and for Excel to
calculate various COUNTIF functions as follows:

I need Excel to lookup column E for all instances where the cells that
contain the text: "PERMANENT" or "FUTURE" or "TEMPORARY" or "FUTURE
DELETION" from the worksheet named: Data.

I also need Excel to lookup column U for all instances where the cells
that contain the text: "Y" from the worksheet named: Data.

If there are any matching records then I need excel to populate the
worksheet (in same workbook) named: Summary as follows:

in cell B8 - I need the COUNTIF function results for "PERMANENT".

in cell B9 - I need the COUNTIF function results for "FUTURE".

in cell B10 - I need the COUNTIF function results for "TEMPORARY".

in cell B11 - I need the COUNTIF function results for "FUTURE DELETION".

in cell B25 - I need the COUNTIF function results for "Y".

The actual COUNTIF calculations and two-way lookup needs to be performed
on worksheet named: Data. Column B contains the Operation data and
column C the Task group data and column E the Position type data and
column U the ROCL Replacement Authorised data.

The actual user input is performed on another worksheet (in the same
workbook) named: Summary. From this worksheet, the user selects the
Operation from the drop-down list at cell B2 and selects a Task Group
from the drop-down list in cell B4.

I want to use the COUNTIF function to calculate from $E$3:$E$65000 and
$U$3:$U$65000 on the worksheet named: Data.

Operations has a defined name = Operations!$A$2:$A$12.

Task Group has a defined name = Groups!$A$2:$A$29.

Any help with this would be greatly appreciated.

Kind regards,

Chris.

 

[A possible solution]

I am not sure whether I was able to follow what you are trying to do (my
solution seems too straightforward...), but have your tried the following?

Cell Summary!B8: =COUNTIF(Data!E$3:E$65000,"PERMANENT")
Cell Summary!B9: =COUNTIF(Data!E$3:E$65000,"FUTURE")
Cell Summary!B10: =COUNTIF(Data!E$#:E$65000,"TEMPORARY")
Cell Summary!B11: =COUNTIF(Data!E$3:E$65000,"FUTURE DELETION")
Cell Summary!B25: =COUNTIF(Data!U$3:U$65000,"Y")

Hope this helped,
Rolf

 

[Chris]

Hi Rolf, thanks for your reply. I am trying to get the user to select
an Operation and a Task Group and need some VB code to do some automatic
lookups and COUNTIF calculations and populate the results into the
worksheet named: Summary in the following cells:

Cell Summary!B8: =COUNTIF(Data!E$3:E$65000,"PERMANENT")
Cell Summary!B9: =COUNTIF(Data!E$3:E$65000,"FUTURE")
Cell Summary!B10: =COUNTIF(Data!E$#:E$65000,"TEMPORARY")
Cell Summary!B11: =COUNTIF(Data!E$3:E$65000,"FUTURE DELETION")
Cell Summary!B25: =COUNTIF(Data!U$3:U$65000,"Y")

The Operation selected will come from column B (worksheet named: Data).

The Task Group selected will come from column C (worksheet named: Data).

The Position Type data (requiring a COUNTIF calculation)will come from
column E (worksheet named: Data).

The ROCL Replacement Authorised data (requiring a COUNTIF calculation)
will come from column U (worksheet named: Data).

The actual user input is undertaken on a separate worksheet named:
Summary where the user selects an Operation (from a drop-down list in
cell B2) and also selects a Task Group (from a drop-down list in cell
B4).

Once the user has selected the Operation and Task Group, then I need
excel vb code to look up the selected Operation and Task Group on the
worksheet named: Data and to automatically perform the COUNTIF
calculations on the worksheet named: Data. Once the calculations have
been performed, then the results need to be populated on the worksheet
named: Summary as described above.

Any help is greatly appreciated.

kind regards,

Chris.