• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Must be a simple answer

M

mad-elph

New Member
I have been wondering how to make an equation that will give me a Year to Date total.

I have a calling log tracker for out call center. Each day I enter the previous day's total call, calls under and calls over 1 Min. I also enter the sales for the day. I would like to add a column that does a Year to date for their sales.

If the excel sheet is laid out vertically, that is the columns are
Agent name, office, date, total calls, <1 min, > 1 min, and sales

I want the next column to be Year to date sales.

So I've tried variations of sumif or vlookups but neither give me the result I want.

Each day I give a report to the sales managers listing calling data. They show their agents, but I want a year to date sales item so they can see where they are.

So, what is the easy formula to do a lookup of agent in Column A to add to a tally for year to date for that specific agent from sales in column O. This new amount will be in column O. No other data needs to impact these. I just can't think of the simple way to do it.
 
Hi, mad-elph!
Consider uploading (a) sample file(s) (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you, relieving the contributor of having to build a test file, if necessary. Thank you.
Regards!
 
Hi ,

When using a SUMIF , don't use the entire range ; use the range from the beginning till the current row , as in :

=SUMIF($A$2:$A2,A2,$F$2:$F2)

assuming that all the dates are in one year , and assuming that column F has the sales numbers. Copy this down.

If all the dates are not in one year , then you need to use a SUMIFS , so that the date column can be checked against date values in the current year , starting from any reference date such as Jan 1 or Apr 1.

Narayan
 
I am really sorry for not uploading, I should know better.

I tried the above, edited them to reflect the actual, A3s and O3s
It worked like a charm. I guess I kept messing up the Criteria. I was making the criteria a vlookup and it wasn't working. I think I was missing the plot when I wrote my code. Must have been late in the night.

Anyway,

NARAYANK991, you are AMAZING!!!!!! Thank you so much. I knew it was simple. Done and dusted. I appreciate your help and anyone else who stops in to look at this. Together we become wiser.
 
keep in mind that Narayan's answer does assume that your data is in Ascending date order
 
Hi Hui,

Yes I noted that, though I believe you mean Descending, and luckily it is. I maintain the call log and add new lines to the bottom of the list each day. A simple drag down of the formula game me my result. Then it unblocked my brain and I was able to add it other metrics that were opened up with it.

Happy happy smiles smiles.
 
You must log in or register to reply here.
Back
Top