Solved: Pivot table or SUMPRODUCT

[Hoopsah]

Hi

I have a spreadsheet, that over the weeks gets added to as timesheets come in. I have been using a pivot table to get the info I require but even that is starting to get ugly now.

What I would ideally like is a formula (Like Sumproduct) that will firstly check the details fall between 2 dates (Column C) that they respond to the correct job i.e. Byelaws (Column L) and what employee they come from (Column B) - if all the above criteria are met it would have to add column K

So, I have a new worksheet with weekly dates running down column B and emplyees names in column D, I have tried:

=SUMPRODUCT(--('QCA - Data Dump'!$C$2:$C$1687>=$B3),--('QCA - Data Dump'!$C$2:$C$1687<$B4),--('QCA - Data Dump'!$B$2:$B$1687=D2),--('QCA - Data Dump'!$L$2:$L$1687="Byelaws"))

but I am struggling to get the SUM Column K in to this formula - and don't even know if this is the best way to go about it. so far the length of the original worksheet is 11327 lines long.

[Bob Phillips]

It is simply

=SUMPRODUCT(--('QCA - Data Dump'!$C$2:$C$1687>=$B3),--('QCA - Data Dump'!$C$2:$C$1687<$B4),
--('QCA - Data Dump'!$B$2:$B$1687=D2),--('QCA - Data Dump'!$L$2:$L$1687="Byelaws"),'QCA - Data Dump'!$K$2:$K$1687)

but in what way is the pivot getting ugly, it will be far more efficient if you have many of these formulae.

[Hoopsah]

Hi Bob,

thanks for that - I think sometimes my brain freezes.

My problem is really that I have to submit a single page showing a sum of all tasks per employee for a single week. I am not really that familiar with Pivots and can see what a formula is doing.

I also wanted this formula to work so that I can design a page where I can have a drop down menu for each employee and have the table calculate itself.

Thanks for your help (Again, again, again)

Cheers

Gerry

[Bob Phillips]

I also wanted this formula to work so that I can design a page where I can have a drop down menu for each employee and have the table calculate itself.


Piviot tables do that intrinsically.

[Hoopsah]

Ok, I am going to give the Pivot Tables another go then. I know everyone keeps telling me they are really great and that I should use them more.

On the up side - I still have your formula for back-up

[Bob Phillips]

If you post your workbook, I will cut a quick one for you.

[Hoopsah]

Hi Bob,

sorry I have not replied sooner - I had a rush visit to the vet yesterday.

I have posted a sample of the workbook that I have, you will see immedietly what I am trying to do.

I have a tab - QCA Data Dump - where I collate all the timesheets, from that I have ran a Pivot for the tasks carried out by the QCA - Tasks Table and another pivot to calculate the mileage - Mileage Table.

Because I am have to break it down into weeks the only thing I have been doing is highlighting the days in the pivot table and for each tab running a =SUM(C5:C9) - then once I have all the fields completed I do a copy-Paste - Values so that they don't change if anything new is added.

I would be interested to see if you know a quicker way to do this - but seriously, it is taking me a while so don't break your back on this.

Cheers Bob

Gerry

[Bob Phillips]

How about this?

[Hoopsah]

Hey Bob,

that is certainly a whole lot tidier - I will really need to work on Pivot tables as I never get mine to give me what I want like that.

So, to get the information into the tables I created for weekly breakdowns would I just use =GETPIVOTDATA or can I put another formula in.

[Bob Phillips]

That is the whole point Gerry, why do you need those breakdowns? Design the pivot well, you hold it all in one pivot, and you drill into that for the particular data that you want.

[Hoopsah]

Hi Bob,

I know what you mean and your pivots are much easier to use, unfortunately I have been given the template to populate for TL's in here to take weekly breakdowns to meetings.

Once I have it up-to-date the pivots you supplied will be perfect for the weekly breakdown of figures.

Thanks for your help on this though, however, I am going to mark it solved as I don't want you to keep trying to solve this when all that is involved is me doing a bit of donkey work.

Needless to say I will be back asking for more help where I really need it soon.

Cheers Bob - Have a good weekend

Gerry

[Bob Phillips]

Sorry to harp on, but didn't my last shot give you the same template as all of your tabs, one tab instead of many?

[Bob Phillips]

I think I posted the wrong version last time, take a look at this one

[Hoopsah]

Uch Bob man!!!

That is absolutely perfect.

Forget what I wrote earlier, this does take all of the donkey work out of it.

Definetly marked solved and I really need to find out how to do Pivot tables properly.

Thanks again Bob

:biggrin: :clap2: :biggrin: :clap2:

Gerry

[Bob Phillips]

Great, I am so glad I convinced you, and sorry about the earlier mess-up, no wonder you were underwhelmed :-)