filtering unique records within subtotal 1

[paulcedarhill]

I have a bid list that may list multiple entries for the same project.

bid date project $ contractor
5/12 a 100 abc const
5/12 a 100 bcd const
5/14 b 300 abc const
5/16 c 100 bcd const

I need to know the total number of unique jobs for the week, along with the total $amount When I nested subtotals it gave me a 4 jobs and $600 when it wshould be 5 jobs and $500. I have to duplicate project entries so I can summraize how much I am bidding to each contractor.

 

[xlbo]

have a look at pivot tables. This is what they were designed for...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[paulcedarhill]

Geof,

I have, but I end up with the same problem, columns of weeks, rows of projects, sum of $ in table,

What am I doing wrong?

 

[paulcedarhill]

Geoff,

For the record I took your advice and looked at other ways to structure the pivot table. By using the contractor as the row, and the week as the column I get the info I need, thought not in the format I originally intended.

Thanks again for the quick response.

 

[paulcedarhill]

Actually pivot tables as I am using them do not solve my problem, they are still adding the same job multiple times.

 

[xlbo]

apologies - on looking closer I can see that it wouldn't quite work

got a question though - how, from your data, do you get a result of 5 jobs and $500 ??? I am assuming that 5/12 stands for 12th May 2006 ??

Given yor data layout, it may be hard to do what I think you want to as it would involve not using certain entries if they are "duplicate" - would that be a correct assumption ? If so, it may still be possible but we may have to create some "helper" columns

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[paulcedarhill]

Sorry, I am trying to do too many things at once as evidence by my previous posts. Based on the sample data there are 3 jobs total $500.

I can add whatever "helper" columns needed to the data. There are "duplicate" entries since we send the same price to multiple contractors.

Your suggestion of pivot table did get me the answer to # of jobs and total amount bid to each contractor that I also need

 

[firefytr]

You can do this with a set of formulas if you'd like.

Assuming your data is in A, in E1:G1 enter these respective labels:
Unique Row, Week Number, Unique Week

In E2 enter:
=COUNTIF($B$2:B2,B2)
Copy down as needed

In F2 enter:
=WEEKNUM(A2)
Copy down as needed

In G2 enter:
=IF(COUNTIF($F$2:F2,F2)=1,1,"")
Copy down as needed

Subtotal data on each change in Unique Week, sum by "$" (column C).

Is this what you're looking for?

-----------
Regards,
Zack Barresse

 

[paulcedarhill]

Zack,

That identifies unique projects , but the G2 value is doing wierd things when there are not more than one project in the week,

I can use your unique project field as criteria for a sumif function, which I can then also set for each weeknum. I will set up summary fields with the sumif formula away from the data table on the spreadsheet, which will work just fine.

Thanks for everybodies help.

 

[firefytr]

Hmm, maybe I had my data structure differently than you. Seemed to work for me, but I may have very well had it wrong. Glad it's working for you now though.

-----------
Regards,
Zack Barresse