re: Excel is easy to learn

quit your bitching and get back to work baw

I use excel all the time.... quit being fricking lazy and google it!

quote:

---

I forgot to tell you that sometimes the word/phrase in column A will have other shite in the cell with it. So it could be axx, axx 438, axx 2/15, etc. I'm trying to get it to pick up on any cell containing axx then grab the corresponding numerical value from B and summing it up in C.

---

Is every abbreviation followed by a space? If so, you can do this:

Column C - extract the abbreviation
In cell C1 enter:

=LEFT(A1,(FIND(" ",A1,1)-1))

Note there’s a space between the quotation marks. This will extract everything to the left of the first space in the cell.

Column D - total for the abbreviation
In cell D1 enter:

=SUMIF(C:C,C1,B:B)

So now column C will tell you the abbreviation found in column A, and column D will tell you the grand total of column B for that abbreviation.

Or you can skip creation of column D and just create a pivot table from columns B and C. Either way works.

If not all of your cells in column A are formatted the same it gets a bit trickier but it’s still doable.

ETA:
Just realized that you said some cells may just contain “axx”. If some are not followed by a space you may need to change column C to this:

=IF(ISNUMBER(FIND(" ",A1,1)),LEFT(A1,(FIND(" ",A1,1)-1)),A1)

This will either pull the text string to the left of the first space in column A into column C, OR it will pull the entire cell in column A into column C if there is no space.

I believe you want the countif and sumif functions.

Hey, excels the shite.
Much better than lotus, visicalc or vp planner.

If you're going to correct him, at least make sure you got it right.............................

Get asap utilities add on for Excel.

Thank me later

quote:

---

Just knowing vlookup and pivot tables makes you a 1%er.

---

It really does. I can do work in 10% of the time it takes for some of my coworkers because knowing vlookups and getpivots makes automation and refreshing data so simple

It makes me look like a genius

I had header for symbol and value columns, do I need one for the pivot table column?