sumproduct and unique fields

[jerry12302]

I have a sample table below to illustrate my problem, the numbers in red are what I'm looking for a formula to calculate, probably a sumproduct, although I'm open to any suggestions.

Weight through conduits are to be summed only once if they are repeated in any particular month. (DEF1000 is in building 2 and 5 in March, but its weight should only be summed once) I hope this makes sense.

systemsfile.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1								Weight	(countconduitsonlyonceifduplicated)
2	Building	Conduit	Type	Weight	Added	Removed			Jan-07	Feb-07	Mar-07	Apr-07
3	BLDG1	ABC999	Metal	100	Dec-06			Metal	100	300	300	100
4	BLDG2	DEF1000	Plastic	100	Mar-07	Jan-08		Plastic	300	400	400	300
5	BLDG3	ABC1001	Metal	200	Mar-07
6	BLDG4	ABC1002	Plastic	200	Apr-06			Detailforillustrationpurposes:
7	BLDG5	DEF1000	Plastic	100	Sep-06	Apr-07		Metal	ABC999	ABC999	ABC999	ABC999
8	BLDG6	ABC1000	Plastic	100	Jun-07					HIJ3000	HIJ3000
9	BLDG7	HIJ3000	Metal	200	Feb-07	Mar-07
10	BLDG8	ABC5000	Plastic	100	Feb-07			Plastic	ABC1002	ABC1002	ABC1002	ABC1002
11	BLDG9	HIJ3000	Metal	200	Feb-07				DEF1000	DEF1000	DEF1000
12										ABC5000	ABC5000	ABC5000
Sheet3

Any suggestions would be appreciated.

 

[facethegod]

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

 

[Fazza]

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

TRANSFORM Sum(A.Weight)
SELECT A.Type
FROM (SELECT DISTINCT MD.Conduit, MD.Type, MD.Weight, ML.TheMonth
FROM MainData MD, MonthsList ML
WHERE MD.Added <= ML.TheMonth AND (MD.Removed Is Null OR MD.Removed>ML.TheMonth)) A
GROUP BY A.Type
PIVOT A.TheMonth

 

[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.

 

[jerry12302]

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.

 

[Fazza]

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

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.

 

[jerry12302]

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.

 

[Fazza]

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

TRANSFORM Sum(A.Weight)
SELECT A.Type
FROM (SELECT DISTINCT MD.Conduit, MD.Type, MD.Weight, ML.TheMonth
FROM MainData MD, MonthsList ML
WHERE MD.Added <= ML.TheMonth AND (MD.Removed Is Null OR MD.Removed>ML.TheMonth)) A
GROUP BY A.Type
PIVOT A.TheMonth

 

[jerry12302]

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.

 

[jerry12302]

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?