Filter & COUNTIF

Hi, I'm a newbie and was wondering if someone would kindly help me with the following?

This post is a Cross-posting http://www.mrexcel.com/forum/showthread.php?t=396211

This is a Cross-Post because when I tried to post this on the Mr Excel Forum, it did not work properly with the MHT files I used. Mr Excel Forum requires an add-in to Excel called: HTML Maker 2.42, however, I was unable to obtain HTML Maker 2.42 from the owner's website or any alternate websites. Plenty of people viewed my message but no replies.

In the attached workbook named: Test 2.xls please view the worksheet named: Summary. On this worksheet the user (user has very little experience in Excel 2003) selects an Operation in cell B2 via a drop-down list and a Task Group in cell B4 via a drop-down list.

After the user has made their selections, I need Excel 2003 to automatically Select worksheet named: Data. I then need it to automatically filter the user selected Operation in column B and the user selected Task Group in column C.

Once the filtering is complete, I need Excel to automatically calculate the COUNTIF functions as follows: on columns E and U and populate the results back on to the worksheet named: Summary as follows:

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")

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

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

Any help would be greatly appreciated,

Kind regards,

Chris

longbow007,

Welcome to the Excel Forum.

See the attached workbook "SUMPRODUCT - Test(1) - longbow007 - SDG.xls".

After the user has made their selections, I need Excel 2003 to automatically Select worksheet named: Data. I then need it to automatically filter the user selected Operation in column B and the user selected Task Group in column C.

On sheet "Data", I am not able to set the data filter to show all, because there is not enough data to play with.

So, I went a different route.

I used "SUMPRODUCT", and had good results with your data.

There is not enough data in sheet "Data" to test the "SUMPRODUCT" formulae on sheet "Summary", in cells B8, B9, B10, B11, and B25.

But, for:
Operation "KINGSTON"
and,
Task Group "WARES"
the "SUMPRODUCT" formula in cell B8, returned 2

Try copying the SUMPRODUCT formulae on sheet "Summary" into a copy of your active workbook as a test, and let me know how you make out.

Then make changes to sheet Summary, cells B2 and B4.

First, use Excel Jeanie ....it works great and is easy to find. Not sure what's going on with HTML maker.

Next, fix the spelling of the Validation lists in Data column E. You've spelling Permanent wrong. You can do that, but you have to spell it wrong on the summary, too.

No macro needed for this. Put this formula in B8 and copy down to B11:

=SUMPRODUCT(--(Data!$B$3:$B$10000=$B$2),--(Data!$C$3:$C$10000=$B$4),--(Data!$E$3:$E$10000=$A8))