Excel woes - automatic sums including dates?
January 18, 2012 12:52 PM Subscribe
In Excel, is there an easy way to make the sum of a column automatically ignore cells that contain date information? Also, is there a way to freeze both the top row and first column so they are always visible?
I have a complicated Excel spreadsheet that I am working on for a family member who thinks that I'm magic. I'm actually just learning as I go along.
This spreadsheet tracks the amount owed, paid, and outstanding for about 100 people who incur monthly bills with him. It did everything that he wanted it to...until yesterday, when he told me that he wants it to also give him totals across all clients for each month.
The sheet is currently set up so that each client has twelve columns, one for each month, and this column is used to track/calculate their business. So, for example, a column for a single client looks like this:
JAN
-550 (amount owed)
250 (amount paid)
300 (automatic sum of the above)
1/4/12 (date paid, which is sometimes just the date, and sometimes a full note if they, for example, made more than one payment)
Each row is then totaled horizontally across Jan, Feb, March, etc so he gets year-to-date totals for each client, and then those totals are summed so he gets year-to-date totals across ALL clients.
I cannot think of an easy way to give him monthly totals across all clients like he wants now. Making formulas by hand by specifying each relevant cell is not realistic due to the number of entries.
I can give him a total monthly balance, which he might be satisfied with, if I could just sum the January (for example) column--but this does not work, as the date information is being treated as a value, which screws the sum up. (Excel is smart enough to ignore the automatic sum cell when it calculates the column's sum, but not the date info!)
So I can see three options:
1. Move the date information to a new column. But then the sheet is hard to navigate and I would really need to find a way to freeze both the top row and first column so that you know where you are. Otherwise, errors are really easy to make. Also, I would have the same date problem with the year-to-date sums, which are done horizontally. (I guess I could put them on a new row, but that's ugly and I do not trust him to put the date in the right place.)
2. Leave the date information where it is and find a way to automatically exclude it in the column sums.
3. Tears, vodka. Ask Metafilter for help.
(Also, since this came up before -- don't worry, I have this set up so I always have a recent backup in case he destroys something irrevocably. And no, he cannot afford a secretary who knows what s/he's doing; stepdaughters are free, and secretaries are not.)
I have a complicated Excel spreadsheet that I am working on for a family member who thinks that I'm magic. I'm actually just learning as I go along.
This spreadsheet tracks the amount owed, paid, and outstanding for about 100 people who incur monthly bills with him. It did everything that he wanted it to...until yesterday, when he told me that he wants it to also give him totals across all clients for each month.
The sheet is currently set up so that each client has twelve columns, one for each month, and this column is used to track/calculate their business. So, for example, a column for a single client looks like this:
JAN
-550 (amount owed)
250 (amount paid)
300 (automatic sum of the above)
1/4/12 (date paid, which is sometimes just the date, and sometimes a full note if they, for example, made more than one payment)
Each row is then totaled horizontally across Jan, Feb, March, etc so he gets year-to-date totals for each client, and then those totals are summed so he gets year-to-date totals across ALL clients.
I cannot think of an easy way to give him monthly totals across all clients like he wants now. Making formulas by hand by specifying each relevant cell is not realistic due to the number of entries.
I can give him a total monthly balance, which he might be satisfied with, if I could just sum the January (for example) column--but this does not work, as the date information is being treated as a value, which screws the sum up. (Excel is smart enough to ignore the automatic sum cell when it calculates the column's sum, but not the date info!)
So I can see three options:
1. Move the date information to a new column. But then the sheet is hard to navigate and I would really need to find a way to freeze both the top row and first column so that you know where you are. Otherwise, errors are really easy to make. Also, I would have the same date problem with the year-to-date sums, which are done horizontally. (I guess I could put them on a new row, but that's ugly and I do not trust him to put the date in the right place.)
2. Leave the date information where it is and find a way to automatically exclude it in the column sums.
3. Tears, vodka. Ask Metafilter for help.
(Also, since this came up before -- don't worry, I have this set up so I always have a recent backup in case he destroys something irrevocably. And no, he cannot afford a secretary who knows what s/he's doing; stepdaughters are free, and secretaries are not.)
If you want, you can add another column that's "month" and another column that's ''year" -- you can pull that out of the date, but if that's too tricky you don't need to, just enter it as you enter each row, and that will make it easier to restrict your pivot table by month or year.
posted by brainmouse at 12:56 PM on January 18, 2012
posted by brainmouse at 12:56 PM on January 18, 2012
To freeze rows and columns go to "View" then "Freeze Panes"
posted by JohnnyGunn at 12:57 PM on January 18, 2012
posted by JohnnyGunn at 12:57 PM on January 18, 2012
Oh, I missed that there was an "amount owed" also. OK, add your "Amount Owed" column and a calculated "Amount Outstanding" column. But every event should be 1 row.
posted by brainmouse at 12:58 PM on January 18, 2012
posted by brainmouse at 12:58 PM on January 18, 2012
This is not a good way to set up a table. Each column should contain one kind of data—money, date, name, that sort of thing. It looks like you've got 3 kinds of data in one column: month, money, date.
Rather than having 12 columns per client representing months, I'd have a billing-event date for each transaction, and sort that into months. And I'd use the same column for payment date.
posted by adamrice at 1:09 PM on January 18, 2012
Rather than having 12 columns per client representing months, I'd have a billing-event date for each transaction, and sort that into months. And I'd use the same column for payment date.
posted by adamrice at 1:09 PM on January 18, 2012
OK, I've thought about it more, and here are the columns I think you should have:
A: Client
B: Month
C: Year
D: Date
E: Transaction Amount
Every time anything happens involving payment, a new row should be made, like this;
"Client" should contain the client's name or ID or whatever
"Month" Should contain the month you're looking at -- the billed month, and the month the payment is applied to, regardless of the date of billing or payment, probably in month/year format, like 1/2012 -- it has to be consistent. Or you can just make it "Jan" and use it in conjunction with the year
"Year" since he likes YTD stuff, just put the year -- 2012
"Date" should be the date of the specific transaction
"Transaction Amount" should be either the amount billed, as a negative number, or the amount paid, as a positive number.
Then you can create a pivot table, with "Client" as your row label, Sum of "Transaction Amount" as your value, and "Month" as a report filter (or, if you decided to just set month to "Jan", both month and year as your report filters -- set it to the month & year you want, and you can see the remaining balance for all of your clients, just like that, with totals at the bottom.
posted by brainmouse at 1:16 PM on January 18, 2012
A: Client
B: Month
C: Year
D: Date
E: Transaction Amount
Every time anything happens involving payment, a new row should be made, like this;
"Client" should contain the client's name or ID or whatever
"Month" Should contain the month you're looking at -- the billed month, and the month the payment is applied to, regardless of the date of billing or payment, probably in month/year format, like 1/2012 -- it has to be consistent. Or you can just make it "Jan" and use it in conjunction with the year
"Year" since he likes YTD stuff, just put the year -- 2012
"Date" should be the date of the specific transaction
"Transaction Amount" should be either the amount billed, as a negative number, or the amount paid, as a positive number.
Then you can create a pivot table, with "Client" as your row label, Sum of "Transaction Amount" as your value, and "Month" as a report filter (or, if you decided to just set month to "Jan", both month and year as your report filters -- set it to the month & year you want, and you can see the remaining balance for all of your clients, just like that, with totals at the bottom.
posted by brainmouse at 1:16 PM on January 18, 2012
OK, more idea: you can also make a "Bill/Payment" column, and just put a "B" or a "P" in there, depending on if it was a bill or a payment. You can then make this a Report Filter also, and choose to only see how much people have been billed, in total, or how much they have paid, in total.
posted by brainmouse at 1:18 PM on January 18, 2012
posted by brainmouse at 1:18 PM on January 18, 2012
You need to use pivot tables. This is going to make your life much, much better.
Brainmouse has the setup that I would use, though I would probably do two options, a Bill/Payment option (put Bill and Payment in there, it will show up more nicely in the pivot table) and then you can show, on the rows, customer names with total billed, total paid by month/year/overall.
I'd hide the month and year column, you can pull them out of the date column automatically and there's no need to ensure he adds them correctly.
There are a few options, really, on how you want to display it -- if you haven't used pivot tables before, there are lots of options online, or I can put together a basic one that looks like your template and mail it to you for you to play/learn with.
posted by jeather at 1:44 PM on January 18, 2012
Brainmouse has the setup that I would use, though I would probably do two options, a Bill/Payment option (put Bill and Payment in there, it will show up more nicely in the pivot table) and then you can show, on the rows, customer names with total billed, total paid by month/year/overall.
I'd hide the month and year column, you can pull them out of the date column automatically and there's no need to ensure he adds them correctly.
There are a few options, really, on how you want to display it -- if you haven't used pivot tables before, there are lots of options online, or I can put together a basic one that looks like your template and mail it to you for you to play/learn with.
posted by jeather at 1:44 PM on January 18, 2012
How big do the dollar amounts get? If the are all smallish numbers, you could just exclude dates by treating it like a number. Since 1/1/2012 = 40909, you could do something like
=SUMIF(A1:A100,"<4>
If the transactions are in the 40000+ range, this becomes more difficult. I can write a sum formula that excludes the dates, but it's the text in the column that fucks me up.
posted by mullacc at 1:49 PM on January 18, 2012
=SUMIF(A1:A100,"<4>
If the transactions are in the 40000+ range, this becomes more difficult. I can write a sum formula that excludes the dates, but it's the text in the column that fucks me up.
posted by mullacc at 1:49 PM on January 18, 2012
This formula would exclude dates by using the CELL function:
{=sumif(isnum(A2:A500)*(left(cell("format",A2:A500),1)<>"D"),A2:A500)/2}
This is an array formula (enter it without the braces, hit CNTL+shift+enter to calculate).
Unpacked: each cell is tested to see if it's a number and not date-formatted, in which case it's added to the total.
Divide by two because you have subtotals in the column, so you're double-counting.
Alternately, if the subtotals come every four rows, this is simpler:
{=sumif(mod(row(a2:a500),4)=2,a2:a500))}
And you'd change "2" to the appropriate number between 0 and 3.
Caveat: on my phone, so these are untested. Should get you started, though.
posted by _Silky_ at 1:51 PM on January 18, 2012
{=sumif(isnum(A2:A500)*(left(cell("format",A2:A500),1)<>"D"),A2:A500)/2}
This is an array formula (enter it without the braces, hit CNTL+shift+enter to calculate).
Unpacked: each cell is tested to see if it's a number and not date-formatted, in which case it's added to the total.
Divide by two because you have subtotals in the column, so you're double-counting.
Alternately, if the subtotals come every four rows, this is simpler:
{=sumif(mod(row(a2:a500),4)=2,a2:a500))}
And you'd change "2" to the appropriate number between 0 and 3.
Caveat: on my phone, so these are untested. Should get you started, though.
posted by _Silky_ at 1:51 PM on January 18, 2012
Best answer: Whoops, html error!
=SUMIF(A1:A100," < 40000 ")
On preview, _Silky_ is getting closer. But I think the text messes up that formula too. I will see if I can modify.
posted by mullacc at 1:56 PM on January 18, 2012
=SUMIF(A1:A100," < 40000 ")
On preview, _Silky_ is getting closer. But I think the text messes up that formula too. I will see if I can modify.
posted by mullacc at 1:56 PM on January 18, 2012
Response by poster: I have taken a screenshot with some dummy info, and uploaded it here, so you can see the layout better:
http://i191.photobucket.com/albums/z219/kutsuwamushi/Screenshot2012-01-18at71442PM.png
Most monthly bills are less than 1000, all are less than 2000.
Some additional clarification:
I would have liked to do this with pivot tables also, but I'm making this for my stepdad, not myself. Saying that my stepdad is not technically savvy would be an understatement. He has no framework for understanding how to navigate a computer. I tried to teach him how to use pivot tables before, but he can't handle it when things don't go 100% as expected 100% of the time. As long as there are simple, precise instructions that work every single time, he's okay--but, for example, he is still flummoxed when he incorrectly chooses whether to single, double, or right click. There is no troubleshooting ability whatsoever.
Therefore, I am trying to make a sheet where all he has to do is enter amounts and dates, which he can do, and all of the summing is done for him automatically. I'm not going to be here to help him every time he needs to do something.
Here are some parameters I can't compromise on:
- Every client must be on the same page
- He cannot enter each client's name and info every month -- this would literally take all day
- Each client's payment history must be visible at a glance
- Each client's payment history must be separated out by month, and include a monthly amount owed, paid, balance
- The date of each payment must be noted somewhere
- Each client must have year-to-date totals of owed, paid, and balance
- Some clients need an additional cell for the amount paid by an assistance program
- There must be year-to-date totals of the above across all clients
- There needs to be a space to make notes, not using the notes tool, which is mysterious and scary
- There needs to be a total arrears for each client that includes year-to-date and previous year's balance
At the beginning of each month, he enters in the amount each client owes. It is usually the same amount, billed on the first of the month--although this sometimes changes as clients leave, come on, or negotiate. Then he enters in the amount and date of each payment as they come in. This gives him running totals.
What I've got actually does all this, although I know it's not laid out according to best--or even good--practice. (I still can't figure out how to do it while keeping all the info in one column/row the same, even after reading your comments--but I will be experimenting tonight and trying to figure out more.) The problem is, now he suddenly wants me to create monthly sums for all clients as well, and that lack of good practice has bitten me in the butt, because it is not easy to do with how it is currently laid out.
I think I can get away with monthly balance across clients only, which is where summing the month columns comes in. I tested it, and excel ignores the balance rows already. Only the dates give me a problem, so I must figure out a good place to move them to or how to exclude them.
(Or completely redo this according to you guys' logic, which I will work on figuring out.)
--
To freeze rows and columns go to "View" then "Freeze Panes"
Could you be more specific? I know how to freeze panes in general, but I need to freeze the top row and first column at the same time, and as far as I can tell the built-in options make me choose one or the other.
posted by Kutsuwamushi at 4:21 PM on January 18, 2012
http://i191.photobucket.com/albums/z219/kutsuwamushi/Screenshot2012-01-18at71442PM.png
Most monthly bills are less than 1000, all are less than 2000.
Some additional clarification:
I would have liked to do this with pivot tables also, but I'm making this for my stepdad, not myself. Saying that my stepdad is not technically savvy would be an understatement. He has no framework for understanding how to navigate a computer. I tried to teach him how to use pivot tables before, but he can't handle it when things don't go 100% as expected 100% of the time. As long as there are simple, precise instructions that work every single time, he's okay--but, for example, he is still flummoxed when he incorrectly chooses whether to single, double, or right click. There is no troubleshooting ability whatsoever.
Therefore, I am trying to make a sheet where all he has to do is enter amounts and dates, which he can do, and all of the summing is done for him automatically. I'm not going to be here to help him every time he needs to do something.
Here are some parameters I can't compromise on:
- Every client must be on the same page
- He cannot enter each client's name and info every month -- this would literally take all day
- Each client's payment history must be visible at a glance
- Each client's payment history must be separated out by month, and include a monthly amount owed, paid, balance
- The date of each payment must be noted somewhere
- Each client must have year-to-date totals of owed, paid, and balance
- Some clients need an additional cell for the amount paid by an assistance program
- There must be year-to-date totals of the above across all clients
- There needs to be a space to make notes, not using the notes tool, which is mysterious and scary
- There needs to be a total arrears for each client that includes year-to-date and previous year's balance
At the beginning of each month, he enters in the amount each client owes. It is usually the same amount, billed on the first of the month--although this sometimes changes as clients leave, come on, or negotiate. Then he enters in the amount and date of each payment as they come in. This gives him running totals.
What I've got actually does all this, although I know it's not laid out according to best--or even good--practice. (I still can't figure out how to do it while keeping all the info in one column/row the same, even after reading your comments--but I will be experimenting tonight and trying to figure out more.) The problem is, now he suddenly wants me to create monthly sums for all clients as well, and that lack of good practice has bitten me in the butt, because it is not easy to do with how it is currently laid out.
I think I can get away with monthly balance across clients only, which is where summing the month columns comes in. I tested it, and excel ignores the balance rows already. Only the dates give me a problem, so I must figure out a good place to move them to or how to exclude them.
(Or completely redo this according to you guys' logic, which I will work on figuring out.)
--
To freeze rows and columns go to "View" then "Freeze Panes"
Could you be more specific? I know how to freeze panes in general, but I need to freeze the top row and first column at the same time, and as far as I can tell the built-in options make me choose one or the other.
posted by Kutsuwamushi at 4:21 PM on January 18, 2012
Best answer: For freezing panes, in recent versions of Excel, you have three options:
1. Freeze only the first row
2. Freeze only the first column
or
3. Freeze panes based on a cell you define.
You want option 3, here's how to do it (assuming Excel 2007 or 2010, for older versions it is slightly different, but still works):
1. select the cell that is just to the right of the column you want to freeze and just below the row you want to freeze. So, if you want to freeze the top row and first column, highlight cell B2. (If you highlight C2 it will freeze row 1 and columns A and B).
2. Go to "View" then click "Freeze Panes" and you should see three options drop down, the first is "freeze panes." Select that one.
3. ????
4. Profit
posted by i love cheese at 4:53 PM on January 18, 2012
1. Freeze only the first row
2. Freeze only the first column
or
3. Freeze panes based on a cell you define.
You want option 3, here's how to do it (assuming Excel 2007 or 2010, for older versions it is slightly different, but still works):
1. select the cell that is just to the right of the column you want to freeze and just below the row you want to freeze. So, if you want to freeze the top row and first column, highlight cell B2. (If you highlight C2 it will freeze row 1 and columns A and B).
2. Go to "View" then click "Freeze Panes" and you should see three options drop down, the first is "freeze panes." Select that one.
3. ????
4. Profit
posted by i love cheese at 4:53 PM on January 18, 2012
Response by poster: Thank you, i love cheese! That is what I didn't know how to did, and it works exactly like I need. So that is one problem down.
I can now probably move the date paid info to a new column, which makes this thing better organized although it doesn't solve everything. (still working on testing out the rest of this advice)
posted by Kutsuwamushi at 5:24 PM on January 18, 2012
I can now probably move the date paid info to a new column, which makes this thing better organized although it doesn't solve everything. (still working on testing out the rest of this advice)
posted by Kutsuwamushi at 5:24 PM on January 18, 2012
This thread is closed to new comments.
Your Spreadsheat should have 3 columns:
A: client
B: date
C: Amount Paid
Then do pivot tables to give sums for different client and date ranges.
posted by brainmouse at 12:55 PM on January 18, 2012