Cumulative total

Question

How can I get formulated cumulative total on a monthly basis in column H and I by adding daily value in column C and DAttached file for reference

mathetes · Answer

Suhel1112&nbsp;&nbsp;I don't believe that's a complete spreadsheet; is it? It only shows one date (3/14/20) so there are no other daily figures to use as a basis for computing a cumulative total. (Presumably, you really are also wanting to make those cumulative totals specific to each of the five sales reps, right?)&nbsp;Anyway, in order for anybody to give you a solid answer, it would help if you could post a copy of the spreadsheet containing data to be accumulated, at least for the month of March.&nbsp;

suhel1112 · Answer

The figures are daily added with the current day date, accordingly needed the Cumulative monthly column to be updated automatically with formula

mathetes · Answer

Suhel1112&nbsp;&nbsp;I'm sorry but it's not at all clear what you mean. Again, you only have one day shown, and your calculation of the totals for that day are accurate. You clearly have data someplace else that is incorporated into the Monthly figures shows in Columns H and I. But you total them accurately as well.&nbsp;I'm sure what you're describing and wanting is clear to you. I'm only saying that I see nothing else amiss in what you've provided in your CRM sample file.&nbsp;I want to reiterate what I said at the start--this only shows one day, so there are no other days to accumulate in any kind of cumulative total. You MUST have some other data somewhere in a more comprehensive set of information on the sales calls made by your sales reps. Where is the rest of the data that is to be totaled on a cumulative basis?

suhel1112 · Answer

@ mathetes.. Thanks for the prompt replyLet's me try to describe in little simple wayI enter the data every data in 2 columns changing the date and what I want is to get the data accumulated in the monthly column automatically, and then so to sum up if I put today's data... The Cumulative should have today's plus yesterday dataOr if u can suggest and better or other format to have cumulative total for 5 sales person

mathetes · Answer

Suhel1112&nbsp;&nbsp;And what I've been trying to ask you in a very simple way is where is the historical data that's to be accumulated? The only way you can write a formula to do a cumulative total is to retain the data to be totaled. You can't obliterate the last day's data, to say nothing of all the prior days, and then expect it to be added to the new day's. (Maybe you could if you depended on a macro or VBA routine, but that really wouldn't be a good idea, for a variety of reasons. Assuming you're the manager of these sales reps (or are acting on behalf of management, you would want to retain historical data for trend analysis, comparative analyses, etc.)&nbsp;Instead, what you need to do is set up your records as a database, with new rows added each day. There are various ways to do this. Doing this also has the benefit of enabling trend analysis, as well as your cumulative totals. I've created a very simple example of a database in the attached, using random numbers. Day by day, you would simply enter the number of calls and conversions per sales rep.&nbsp;Once you've done that, depending on how you want to analyze the data, there are various ways to extract and calculate. A very basic way is the Pivot Table. And again, I've created a very simple example of that. No formula needed; but you will need to do some research in Excel help (or some manual on Excel features) into setting up Pivot Tables. All I did was show how the Pivot Table will produce a cumulative total for each column (calls and conversions). I didn't do the percentages; you've demonstrated that you know how to write that formula.&nbsp;There are other ways to accomplish this kind of summary, and it could be an analysis by month (or by week), by sales rep.......it all kind of depends on how "granular" or detailed you want to be. But the heart of this kind of analysis is a database--a series of records of the daily events--that will serve as the basic data to be analyzed.&nbsp;So all I've given you here is a start at some methods to accomplish what you appear to want. Were I (or anybody else) to work with you more, we'd need to spend some time setting out your goals or objectives--what over the longer term you'd want to be able to see and understand--but I can't overstate the importance of having a database at the heart of it. You can't produce a summary report without data to summarize having been accumulated. Excel is fantastic at producing that kind of summary, once you've set it up correctly.

Forum Discussion

Cumulative total

6 Replies

Resources