Count Multiple Entries, Return Multiple Rows, Based On 3 Criteria

**gtj_global** · 09-19-2008, 06:15 PM

Hi everyone,
I have been working on this formula for a while but cannot seem to get it correct.
I have 3 sheets.
Sheet 1 - some codes and dates to be used in Sheet 3 in dropdown lists. Simple enough.
Sheet 2 - the data (column A is Family Code, Column B is Item Code, Column 3 is # Sold, and Column D is Date Sold. There are about 200 rows with duplicates of item code.
Sheet 3 - I need a report based on the following:
I would like the user to select the family code, the report start date and the report end date from dropdown lists. This would in turn display the item code and the total number sold (from highest to lowest OR I can sort later).

How complicated is this? I was hoping to keep this simple for the user.
The file is attached.

Any help would be GREATLY appreciated. Thank you.
Regards, GTJ

**MartinShort** · 09-19-2008, 08:26 PM

I'd try something like the attached.

I used the Advanced Filter rather than autofilter (if you aren't familiar with it then you will need to read Excel's help pages on it)

To make things easier for your users I wrote two simple macros that apply the filter and remove the filter.

I also simplified your lookup sheet by changing your named range to

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),1)

which will make the lookup dynamically increase in length when you add new items. This will remove the blank rows you had at the bottom of your lookup.

HTH
Martin

**gtj_global** · 09-20-2008, 01:38 AM

Martin, thanks for your reply...!!!
This is better than before for sure.
But now there are duplicate entries for some rows, which is fine.
But I need not to display the duplicates (not delete them) and total the number of times they appear.
I was thinking of a pivot table but it is a little complicated for the users.
What's your expert opinion? :-)
Thanks again - GTJ

**MartinShort** · 09-20-2008, 03:53 AM

Not as expert as you might think - just a learner! Thanks though

OK - I've done a couple of things to your spreadsheet.

1. Put a Sheet Level bit of code to monitor if the cell A4 changes. If it has, it will automatically fire the FilterOn macro.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$4" Then
        FilterOn
    End If
End Sub

2. I created another dynamic range called "Table" - same methodology as before. I then changed the FilterOn macro to reference the range names rather than you having to modify the macro everytime the spreadsheet changes. (Good practice!)

3. I added a DSUM formula to the sheet, which works pretty well on the same principle as the criteria selection for Advanced Filter. This avoids the use of Pivot tables altogether; taking out one level of complexity for your users. It's worth reading up on the database formulae - DSUM, DCOUNT, DGET etc. as they're incredibly useful once you get into them.

This should hopefully give you some ideas to finish off.

Martin

**gtj_global** · 09-20-2008, 05:26 AM

Martin, this is great. I understand it more now.

I did review DSUM, DCOUNT, DGET but was not sureif I could use them here.

I want to put the finishing touches on the spreadsheet; however, the frields/macros that you entered are not updating/working properly. They are not updating (specifically D6, A9 and if my end date is August 15, the report still displays items purchased on August 18). I am using Office 2007... could it be a version problem or otherwise?

Thanks again. You've been very helpful.
- GTJ

**MartinShort** · 09-20-2008, 08:08 AM

Glad to be of help.

I couldn't follow your last post though. Are you still using the version I last posted or have you made modifications to suit your own needs? I didn't touch your own report sheet in the end as I was using "August 2008" and "Data" only.

I tested my version with

August2008!A4 = "02-Boxer"
August2008!B4="5/8/08"
August2008!C4="15/8/08"
Click "Filter On"

Four results were returned: three for the 6th August and one for the 15th. This is what I expected to see; there weren't any "18th" dates listed.

If you need to, can you re-post the spreadsheet with specific instructions as to what keystrokes you entered and I'll try duplicating it?

With regards to Excel 2007, I haven't upgraded yet, but I wouldn't have thought it would have made a difference.