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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.

**gtj_global** · 09-20-2008, 12:22 PM

Martin,
Actually I changed nothing in the file after you made the changes.
The requirements for the spreadsheet has changed a little.
The users failed to indicate that the last 2 digits of the Item Code should not be included (this is the size of the item and should not be included in the report).
So, once this change has been made there will be more duplicates in the list after filtering.
The idea is to choose a family, then a start date and end date of the report and get a list with no duplicates, but a total of items sold per item code.
I will update the sheet removing the last 2 digits of the item code. Do you think a spreadsheet can still handle this?
Thanks again for all your help. You've been great!
- GTJ

**MartinShort** · 09-20-2008, 06:40 PM

Oh I reckon it will work. If you needed to you could remove duplicates and count/filter on the Item Code instead of, or as well as, Family. The nice thing about Advanced Filter and DSUM is you can add as many criteria as you like (someone might shoot me down in flames on that as I'm assuming you will never run into an upper limit that would require a workaround.)

If you don't want to remove the last two digits of the Item Code, you can update your DSUM and filter criteria to work on

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

*Where ITEMCODE will need to be set up as a named range.
(you can future proof this by using the second formula if ITEMCODE is likely to change length in the future!)

More than happy to help. I got into this rather unusual pastime as a means to improving my own skills by attempting to solve problems for other people. It's amazing - and sometimes frustrating - how often you come up with a solution and then someone else will post a much better one. It's a fast way to learn.

I'm still not sure we got to the bottom of your "...specifically D6, A9..." post, as my spreadsheet doesn't seem to reference D6, but hopefully you're ok with that now.

Martin