Today's Excel Q: adding items in a column based on info in a row

Factual Questions
1

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.

2

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.

3

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.

4

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.)

5

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)

6

Perfect!!

Thanks a million!!

7

Mr. Blue Sky
You are welcome.
I’m glad I was able to help you out.

8

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: :slight_smile: :wink: :eek: :smiley:

9

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)

10

Well, now that that’s done, any idea how to adapt this to Open Office 2.0?

11

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

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.

12

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.

13

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

14

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 !!!

15

Cool, that works and it isn’t case sensitive.

16

That’s because they’re regular expressions, not wildcards. A relevant tutorial. A Wikipedia article brimming with references elsewhere. Ask me if you want to know more.