# Today's Excel Q: adding items in a column based on info in a row **URL:** \ **Category:** Factual Questions\ **Created:** [January 12, 2006, 11:44pm UTC](https://boards.straightdope.com/t/todays-excel-q-adding-items-in-a-column-based-on-info-in-a-row/339736 "2006-01-12T23:44:21Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![Mr.Blue\_Sky](https://avatars.discourse-cdn.com/v4/letter/m/d26b3c/32.png) [@Mr.Blue\_Sky](https://boards.straightdope.com/u/Mr.Blue_Sky)\ **Post date:** [January 12, 2006, 11:44pm UTC](https://boards.straightdope.com/t/todays-excel-q-adding-items-in-a-column-based-on-info-in-a-row/339736/1 "2006-01-12T23:44:21Z")
I’m trying to help a co-worker tweak a spreadsheet. It contains information about appealed claims. It must be sorted by the patient’s name for use by the head of case management, but she would like to show totals for each insurance company at the bottom. One of the insurance companies is Blue Cross. There several different types of Blue Cross, but they need to be added together. I need a formula like: If (contents of column D starts with “BC”) then add the figure in Column E. I could then modify the formula to be used for the other insurance companies. ---
**Author:** ![Jpeg\_Jones](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@Jpeg\_Jones](https://boards.straightdope.com/u/Jpeg_Jones)\ **Post date:** [January 13, 2006, 12:25am UTC](https://boards.straightdope.com/t/todays-excel-q-adding-items-in-a-column-based-on-info-in-a-row/339736/2 "2006-01-13T00:25:11Z")
Access would be better for this type of thing, but no matter… I’d simply sort the spreadsheet on the “BC…” column, and insert a few spaces for a subtotal. If you don’t like that, try creating a new column, and fill down the following formula (substitute cell references as needed): =if(left(a1,2)=“BC”,e1,"") Then tally that column up. Something along those lines. ---
**Author:** ![OldGuy](https://avatars.discourse-cdn.com/v4/letter/o/3bc359/32.png) [@OldGuy](https://boards.straightdope.com/u/OldGuy)\ **Post date:** [January 13, 2006, 1:52am UTC](https://boards.straightdope.com/t/todays-excel-q-adding-items-in-a-column-based-on-info-in-a-row/339736/3 "2006-01-13T01:52:07Z")
The Excel function DSUM was designed to do this. I’m not sure if it works on a portion of a cell though as you seem to indicate you want it to. You might hav eto set up another column holding just the two characters you want to focus on. ---
**Author:** ![wolf\_meister](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/wolf_meister/32/15202_2.png) [@wolf\_meister](https://boards.straightdope.com/u/wolf_meister)\ **Post date:** [January 13, 2006, 4:59am UTC](https://boards.straightdope.com/t/todays-excel-q-adding-items-in-a-column-based-on-info-in-a-row/339736/4 "2006-01-13T04:59:19Z")
I’d say the SUMIF function is the function to investigate. I believe it would be (in your case) =SUMIF(D1:D10=“_BC_”, E1:E10) The function searches the _range_ (cells D1 through D10) and if it finds a match, it will add it to whatever cell the SUMIF function is in (for example cell E11). I think I have it typed correctly but if it doesn’t work, look it up on the HELP section because the syntax must be _exact_. (Also note the use of “wild cards” around the search term.) ---
**Author:** ![wolf\_meister](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/wolf_meister/32/15202_2.png) [@wolf\_meister](https://boards.straightdope.com/u/wolf_meister)\ **Post date:** [January 13, 2006, 5:12am UTC](https://boards.straightdope.com/t/todays-excel-q-adding-items-in-a-column-based-on-info-in-a-row/339736/5 "2006-01-13T05:12:55Z")
The smilie got in there because it is produced by the colon D. Anyway, I just wrote a quick formula on my own computer and here is the proper syntax: (To avoid another smilie, I changed the columns to E and F.) =SUMIF(E1:E10,"=_bc_",F1:F10) ---
**Author:** ![Mr.Blue\_Sky](https://avatars.discourse-cdn.com/v4/letter/m/d26b3c/32.png) [@Mr.Blue\_Sky](https://boards.straightdope.com/u/Mr.Blue_Sky)\ **Post date:** [January 13, 2006, 3:27pm UTC](https://boards.straightdope.com/t/todays-excel-q-adding-items-in-a-column-based-on-info-in-a-row/339736/6 "2006-01-13T15:27:03Z")
> [@wolf\_meister](#): > > The smilie got in there because it is produced by the colon D. > Anyway, I just wrote a quick formula on my own computer and here is the proper syntax: > (To avoid another smilie, I changed the columns to E and F.) > > =SUMIF(E1:E10,"=_bc_",F1:F10) Perfect!! Thanks a million!! ---
**Author:** ![wolf\_meister](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/wolf_meister/32/15202_2.png) [@wolf\_meister](https://boards.straightdope.com/u/wolf_meister)\ **Post date:** [January 14, 2006, 3:02am UTC](https://boards.straightdope.com/t/todays-excel-q-adding-items-in-a-column-based-on-info-in-a-row/339736/7 "2006-01-14T03:02:00Z")
**Mr. Blue Sky** You are welcome. I’m glad I was able to help you out. ---
**Author:** ![DarrenS](https://avatars.discourse-cdn.com/v4/letter/d/977dab/32.png) [@DarrenS](https://boards.straightdope.com/u/DarrenS)\ **Post date:** [January 14, 2006, 6:50pm UTC](https://boards.straightdope.com/t/todays-excel-q-adding-items-in-a-column-based-on-info-in-a-row/339736/8 "2006-01-14T18:50:09Z")
> [@wolf\_meister](#): > > The smilie got in there because it is produced by the colon D. For future reference, there’s a check-box that’s not very prominent under “miscellaneous options”, below the post box, for “disable similies in text”. Once you check it, you can post normally-smiley-generating sequences of characters with impunity: 🙂 😉 :eek: 😃 ---
**Author:** ![wolf\_meister](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/wolf_meister/32/15202_2.png) [@wolf\_meister](https://boards.straightdope.com/u/wolf_meister)\ **Post date:** [January 14, 2006, 7:57pm UTC](https://boards.straightdope.com/t/todays-excel-q-adding-items-in-a-column-based-on-info-in-a-row/339736/9 "2006-01-14T19:57:03Z")
Thanks **Darren** I always wondered why that box was there. To me it seemed superfluous. If I didn’t want smilies I wouldn’t put those in to begin with. (A button I use quite frequently _but not that time_ was preview). :smack: It looked like a straightforward text posting so I didn’t need to use preview right? (guess not) ---
**Author:** ![Mr.Blue\_Sky](https://avatars.discourse-cdn.com/v4/letter/m/d26b3c/32.png) [@Mr.Blue\_Sky](https://boards.straightdope.com/u/Mr.Blue_Sky)\ **Post date:** [January 14, 2006, 8:01pm UTC](https://boards.straightdope.com/t/todays-excel-q-adding-items-in-a-column-based-on-info-in-a-row/339736/10 "2006-01-14T20:01:25Z")
Well, now that that’s done, any idea how to adapt this to Open Office 2.0? ---
**Author:** ![wolf\_meister](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/wolf_meister/32/15202_2.png) [@wolf\_meister](https://boards.straightdope.com/u/wolf_meister)\ **Post date:** [January 14, 2006, 11:20pm UTC](https://boards.straightdope.com/t/todays-excel-q-adding-items-in-a-column-based-on-info-in-a-row/339736/11 "2006-01-14T23:20:24Z")
Well **Mr Blue Sky** , you got my curiosity going so I downloaded openoffice. I don’t know about you, but the “HELP” section on mine is all screwed up. So I surfed the 'Net and found this helpful website: [http://www.openofficetips.com/blog/archives/2004/12/basic\_functions\_7.html](http://www.openofficetips.com/blog/archives/2004/12/basic_functions_7.html) It seems the subtle difference is that you use semi-colons in place of the commas. The formula then becomes: =SUMIF(E1:E10;"=_bc_";F1:F10) HOWEVER, another problem is that I don’t know how “wild cards” are handled. Anyway, I hope this gives you a good start. ---
**Author:** ![Mr.Blue\_Sky](https://avatars.discourse-cdn.com/v4/letter/m/d26b3c/32.png) [@Mr.Blue\_Sky](https://boards.straightdope.com/u/Mr.Blue_Sky)\ **Post date:** [January 14, 2006, 11:44pm UTC](https://boards.straightdope.com/t/todays-excel-q-adding-items-in-a-column-based-on-info-in-a-row/339736/12 "2006-01-14T23:44:23Z")
> [@wolf\_meister](#): > > Well **Mr Blue Sky** , you got my curiosity going so I downloaded openoffice. > I don’t know about you, but the “HELP” section on mine is all screwed up. > So I surfed the 'Net and found this helpful website: > [Basic functions : SUMIF – Open office tips](http://www.openofficetips.com/blog/archives/2004/12/basic_functions_7.html) > > It seems the subtle difference is that you use semi-colons in place of the commas. > > The formula then becomes: > =SUMIF(E1:E10;"=_bc_";F1:F10) > > HOWEVER, another problem is that I don’t know how “wild cards” are handled. > > Anyway, I hope this gives you a good start. Interesting. I tried it on my comic book database and it definitely doesn’t like wildcards AND it appears to be case sensitive. Thanks for the help. I’ll have to play with it some more. ---
**Author:** ![China\_Guy](https://avatars.discourse-cdn.com/v4/letter/c/779978/32.png) [@China\_Guy](https://boards.straightdope.com/u/China_Guy)\ **Post date:** [January 15, 2006, 1:58am UTC](https://boards.straightdope.com/t/todays-excel-q-adding-items-in-a-column-based-on-info-in-a-row/339736/13 "2006-01-15T01:58:01Z")
If you’re really interested in becoming a spreadsheet jockey, look into pivot tables. I have no idea on how one makes pivot tables though. However, pivots are prevalent in my company and I use them every day to access data. here’s an intro link: [http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=553](http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=553) ---
**Author:** ![wolf\_meister](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/wolf_meister/32/15202_2.png) [@wolf\_meister](https://boards.straightdope.com/u/wolf_meister)\ **Post date:** [January 15, 2006, 2:42am UTC](https://boards.straightdope.com/t/todays-excel-q-adding-items-in-a-column-based-on-info-in-a-row/339736/14 "2006-01-15T02:42:59Z")
It seems the wild cards in Open Office are periods AND asterisks. Here is the formula with the wild cards: =SUMIF(E1:E10;"=._bc._";F1:F10) I don’t think it is case sensitive though. Hey this Open Office “suite” of programs is pretty damned good for free !!! ---
**Author:** ![Mr.Blue\_Sky](https://avatars.discourse-cdn.com/v4/letter/m/d26b3c/32.png) [@Mr.Blue\_Sky](https://boards.straightdope.com/u/Mr.Blue_Sky)\ **Post date:** [January 15, 2006, 2:46am UTC](https://boards.straightdope.com/t/todays-excel-q-adding-items-in-a-column-based-on-info-in-a-row/339736/15 "2006-01-15T02:46:46Z")
> [@wolf\_meister](#): > > It seems the wild cards in Open Office are periods AND asterisks. > > Here is the formula with the wild cards: > > =SUMIF(E1:E10;"=._bc._";F1:F10) > > I don’t think it is case sensitive though. > > Hey this Open Office “suite” of programs is pretty damned good for free !!! Cool, that works and it isn’t case sensitive. ---
**Author:** ![Derleth](https://avatars.discourse-cdn.com/v4/letter/d/b9e5f3/32.png) [@Derleth](https://boards.straightdope.com/u/Derleth)\ **Post date:** [January 15, 2006, 3:53am UTC](https://boards.straightdope.com/t/todays-excel-q-adding-items-in-a-column-based-on-info-in-a-row/339736/16 "2006-01-15T03:53:15Z")
> [@wolf\_meister](#): > > It seems the wild cards in Open Office are periods AND asterisks. That’s because they’re regular expressions, not wildcards. [A relevant tutorial.](http://www.openofficetips.com/blog/archives/2005/11/regular_express.html) [A Wikipedia article brimming with references elsewhere.](http://en.wikipedia.org/wiki/Regular_expression) Ask me if you want to know more.