sumproduct and unique fields

So for PLASTIC'S in Feb-07 try something like


=SUMPRODUCT(--(COUNTIF(OFFSET(B2:B10,,,ROW(B2:B10)-ROW(B2)+1,1),B2:B10)=1),--(C2:C10="plastic"),--(TEXT(E2:E10,"mm/yy")="02/07"),D2:D10)


HTH

Jerry,

I have a different answer from what you posted - so can you check the result is correct?

Approach: give the source data table "A2:F11" a defined name, I used "MainData". Add another table with header "TheMonth" and dates listed under it: 1 Jan 07, 1 Feb 07, 1 Mar 07m 1 Apr 07. Give this entire table (header & 4 data items) a defined name, I used MonthsList.

Save the file. Via menu data, import external data, new database query select Excel files, then browse & select your Excel file, follow the wizard, select at least one field from the tables, proceed until you get a choice to finish but don't finish yet. Select the option to go into MS Query. When that starts hit the SQL button and copy the SQL from below to fully replace what you see. Enter this (acknowledging any message you get and continue). You should see the result set within MS Query. Now hit the open door icon & put the results set into a worksheet. This is now a refreshable query table.

HTH, Fazza

PS The query table, or similar, database-type approach will be scalable. So, it will work quickly with 10 records or 60,000 records. A formulaic solution will struggle if you have large data sets though might be OK if you don't have much data.

Thanks Face, I'll try that.

Thanks Fazza, I followed your steps and made it as far as "follow the wizard, select at least one field from the tables". By select I assume you mean select the fields and press > to get the fields in the window to the right labeled "Columns in your query". That worked, but when I hit Next I got the error message:

"The Query Wizard can not continue because it can not join the tables in your query. You must join the tables manually in Microsoft Query by dragging the fields to join between the tables."

I've never messed with this before, any suggestions? I'm stumped.

"The Query Wizard can not continue because it can not join the tables in your query. You must join the tables manually in Microsoft Query by dragging the fields to join between the tables."

I've never messed with this before, any suggestions? I'm stumped.

Click to expand...

Yes, Jerry. Per my earlier post, select at least one field from the tables, proceed until you get a choice to finish but don't finish yet. Select the option to go into MS Query. When that starts hit the SQL button and copy the SQL from below to fully replace what you see. Enter this (acknowledging any message you get and continue). You should see the result set within MS Query. Now hit the open door icon & put the results set into a worksheet. This is now a refreshable query table.

That is, just acknowledge the message you got and continue.

Fazza, I finally stumbled into MSQuery, I hit the SQL button and replaced the code there with the code you gave me. Then I pressed OK and got this message:

"SQL query can't be represented graphically. Continue anyway?"

When I press OK I get the message:

"Could not add the table '(SELECT'."

Any ideas? Thanks for your help, sorry for being dense on this, this SQL stuff is all new to me.

Hi, Jerry.

It is critical that you have your table names and field names EXACTLY the same as I have used. That is, one table called MainData (headers & data all in the named range, not a dynamic named range) with headers "Conduit", "Type", "Weight", "Added" & "Removed" and one table called "MonthsList" (headers & data all in the named range, not a dynamic named range) with header "TheMonth". If you have: different names; or spaces added in names; or table named ranges that do not correctly cover the headers & data - it won't work. (Of course, the SQL can be changed to suit different names if you like.)

Suggest you try again. If it fails again, please post the SQL you are using. So, copy & paste it into your thread as 'code' like below.

I'll separately post again - in the next hour or so - with some VBA to create the table.

regards, Fazza

Hey! I got it, boy, the devil is in the details. I placed the result set into the spreadsheet. The only problem I see is that it is not accounting for the removed field. Metal for March, for example, should be 200 less because 200 was removed in March. Is there a way to account for this?

Thank you so much for getting me this far.

Another question, can the result set be placed into the same spreadsheet the tables are in? I tried that, and I tried changing some source data and refreshing the data, but the numbers didn't change. Am I doing it wrong?