Adding a set of numbers in one column based on what is in adjacent column

[cmccambridge]

Start Time	End Time	Hours	Rate
10:30 AM	3:30 PM	5	1
5:00 PM	10:00 PM	5	1
12:00 PM	8:00 PM	8	1
9:30 AM	1:00 PM	3.5	1
1:00 PM	5:00 PM	4	1
7:00 PM	9:00 PM	2	1
10:15 AM	2:00 PM	3.75	2
2:30 PM	4:30 PM	2	1
5:00 PM	10:00 PM	5	2
12:00 PM	2:30 PM	2.5	1

Hours is column G and Rate is Column H. I need a formula that adds total hours for rate 1, then in another cell rate 2 and s on. Column G goes from 114 to 150

Thanks!!

[JeffreyWeir]

Pivot tables were meant for this stuff.

If you’ve got Excel 2007 or later, then the first thing you want to do is turn your datasource into an Excel Table. To do this, select your original data, and push Ctrl + T, make sure the ‘My Table has headers’ checkbox is checked, the click OK.

(Aside: the reason we use a table is that they auto expand if you put new data at the bottom of them).

Then select a cell in that table, and click “Insert Pivot Table” from the ribbon, then click OK.

Then in the PivotTable Field list that appears on the right hand side, drag ‘Rate’ down to the ‘Row Labels’ section, and drag ‘Hours’ to the ‘Values’ section.

All done.

Any time you add new data to the table, just right click over the pivot table and select 'Refresh'.

Probably best you do a bit of reading on the web about pivottables. They are the best think about Excel.

See attached.

Book1.xlsx

[Hercules1946]

@JefffreyWeir
Thanks for your posting. Ive always been a bit wary of pivot tables because I seemed to get problems when I wanted to refresh the data or alter the structure of the table, so I think I fell back on
what I was more familiar with. You've definitely convinced me to try again.

Hercules

[Kevin@Radstock]

Hi cmccambridge

Use the SUMIFS, for example and assuming your data is in E1:H11 (Inc. Headers): =SUMIF(H2:H11,1,G2:G11)

Or another option in I2 and copy down: =IF(COUNTIF($H$2:$H$100,H2)=COUNTIF($H$2:H2,H2),SUMIF($H$2:$H$100,H2,$G$2:$G$100),"")

[Pecoflyer]

Probably best you do a bit of reading on the web about pivot tables. They are the best think about Excel.

That is a matter of opinion.OTH if your number of rows increases the PT might get slow or even refuse to work. ( I think there is a limit to the admissible nr of rows)

So, IF you have XL2010 download and install the free MS PowerPivot add-on. You can work with millions ( yes, millions ! ) of rows, and results are created in the blink of an eye.

[JeffreyWeir]

To be sure, PowerPivot is the most significant advancement to Excel for well over a decade...if you have Excel 2010. That said, the output of PowerPivot is a pivot table.

[JoePublic]

Or if you have Office 2013 ProPlus (no other version of 2013 will do).

While I agree with your statement in theory, in practice I think PP has a long way to go before it is truly usable by most people. It is currently, in my opinion, too complicated, buggy and feature-restricted even to suggest as a full time replacement for regular pivot tables and until it can do that, it will be of little use to the majority of people - even those who have access to it.

[JeffreyWeir]

In practice I think PP has a long way to go before it is truly usable by most people..

Heh...I'd argue it's the other way around. Most people have a long way to go before they are able to use PP. Hell, most of the analysts I work with don't know enough about Pivot Tables or even Excel Tables, never mind PP ;-)

My point above about PivotTables being the "best" thing about Excel certainly is a matter of opinion. And PP certainly lets you mash up more data from more places, using more languages than we had access to before. And that's not to be underrated. But PowerPivot is quite a step up from PivotTables, and PivotTables are quite a step up from doing analysis without PivotTables. So I'm happy to opine that Pivottables are the best thing about Excel in the context of this particular forum post. In another forum post - in another question context - I'd say that VBA was the best thing about Excel. But for this poster, I think looking into Pivot Tables might well be the best addition to their Excel toolset for their money. I certainly wouldn't be pointing them to PowerPivot...even though PowerPivot is to PivotTables what the rocket engine is to the wheel.

Pivottables are like the gateway drug of higher data analysis. Lets think about what a Pivot Table does: it allows allow intermediate users who have little or no understanding of formulas or SQL to filter, summarize, and slice like an SQL pro and/or formula Jedi. And it allows it right out of the box using pretty much drag and drop. No advanced SQL neccessary, and - like with a car - you only need to know where to input the gas. No understanding of the underlying mechanics required.

I've seen a lot of interesting posts on quite tricky problems answered with some very tricky formulas that the user probably could not have come up with on their own even given their current understanding of formulas. And for some of those problems, I've noticed that a pivot would allow pretty much the same thing; but the difference being that the user could easily grasp the basics in a matter of hours or days, rather than the months or years to get up to a similar output-enabling level using formulas.

[JoePublic]

Heh...I'd argue it's the other way around. Most people have a long way to go before they are able to use PP.

But if you're trying to sell a product, that's not a good standpoint! Better and easier to try and make the product simpler than to try and make people smarter.

Hell, most of the analysts I work with don't know enough about Pivot Tables or even Excel Tables, never mind PP ;-)

An analyst who doesn't understand pivot tables is not an analyst.

PowerPivot is to PivotTables what the rocket engine is to the wheel.

The problem being you have to design and build the engine yourself as well as figure out how to steer it...

[JeffreyWeir]

THe last two of those points I agree with heartily. :-) On the first point, I don't mind too much that MS are providing an advanced bit of kit that will be over the head of most casual users. After all, those casual users probably best stick to pivottables and VLOOKUPS.


The thing I feel more than a little disconcerted with about PowerPivot is that when I glance over great blogs like www.powerpivotpro.com and listen in to the evangelists talking about how cool it is, then I feel I'm getting left behind by NOT embracing PowerPivot and learning its ins and outs. All that talk of MDX and DAX and innovative ways to show this and that - when I haven't had need nor time to delve in - is like being at a party and everybody is making in-jokes that go over your head.