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

Sum up Sales Amount Occurred in Same Date under A Sales Person

M

maniknandi

Member
I have a sales Database as under:

[pre]
Code: 
Date SalesPerson Vendor SalesAmount
4/1/13    A      XYZ Ltd.  4500
4/3/13    B      AAA Ltd.  3000
4/3/13    B      BBB Ltd.  3000
5/3/13    A      BBB Ltd.  2500
6/1/13    B      AAA Ltd.  5000
I want to extract data from the above database based on the selection of the month. If I select April' 13 it will show as below:

Date SalesPerson SalesAmount
4/1/13    A          4500
4/3/13    B          6000 (Sum up sales amount happened in same date under a salesperson)
[/pre]
I want to do this through formula only. Please help me and thanks in advance.


Edited with backticks
 
Hi Maniknandi


OK so you are not open to a pivot table? Formula - If your date was in E1 and your Letter A, B etc was in F1 and you are using XL 07 or later then;


=SUMIFS($D$2:$D$15,$A$2:$A$15,E1,$B$2:$B$15,F1)


If you are using XL 03 then the following.


=SUMPRODUCT(($A$2:$A$15=E1)*($B$2:$B$15=F1)*($D$2:$D$15))


Take care


Smallman
 
Hi Manik


If you wanted to get the unique dates in a list I would get it by using the Advanced filter. Highlight the Date Column, Go Data - Advanced -


Copy to Another Location

Copy to H1 for example

Tick Unique Records only


Ensure you do these three things and you have a unique list of dates. Done and dusted.


Take care


Smallman
 
Good day maniknandi


If you turn your data in to a table you could filter on anything you wanted to. Date, SalesPerson, Vendor or SalesAmount in any way you wanted to and then place the filtered data where you wanted. You could have unique lists of anything.
 
Thanks everyone,


I can use Pivot Table or Auto Filter that can solve my problem but I want to do it through formula only.


Thanks,
 
Hi Manik ,


Check out this link :


http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/


Narayan
 
Manik


If you want to do it with formula only then did I miss something? I provided a formula that does precisely what you requested. Two options are before you depending on your version of XL. Now what is it exactly that you want if it was not contained within that post? In my opinion you have the answer to your original request. Looking forward to hearing where the above falls over or any divergence from the original request.


Take care


Smallman
 
Dear Smallman,


I want to add another request with my original request. I want to select a Sales Person in this case "A", the excel will extract an unique list of dates against "A" from my raw data and sum up amounts if sales has been made more than one time in that particular day. Similarly if I select "B" will show the B's data. For example: If I select "B" it will show following data....


Date Sales Amount

4/3/13 6000

6/1/13 5000


Thanks in advance.


Manik
 
Hi Manik


Does this have to be done with formula? I am sure it is possible with formula but I could write a simple bit of code which does the what you request in no time at all. I am not sure why you are adverse to using a pivot table or filters. This would be the way to get your data in the most expedient manner.


Take care


Smallman
 
Dear Smallman,


The main reason is I want to use this formula in my dashboard. It should be dynamic, Yes I like to use formula instead of pivot table.


Thanks,


Manik
 
Lets have a look at this dashboard then. Please don't hide behind the 'it is sensitive information' line. If you keep the same structure of your raw data and dashboard it should only take a few minutes to make it generic. If you do what I ask you will get an excellent response from the forum.


Take care


Smallman
 
Manik


Where is the dashboard? Why it is below;


"The main reason is I want to use this formula in my dashboard"


Should not be too hard to find it appears it is on your computer.


Take care


Smallman
 
Sorry Smallman, I did not understand. I thought you have sent any dashboard for example. Well my dashboard is under preparation and I did not use any pivot table there. Although frankly speaking I do not like to use pivot table, I love formulas.


Thanks,


Manik
 
Hi Manik


I agree for a dashboard pivot tables are not the best option. I only use pivot tables for getting figures quickly I do not incorporate them into any of my modelling. They are very handy to cross check data and though. But for your problem where you have to a dynamic list of dates I would do it like this. I would create a range in your dashboard lets give it a header called rather originally "Dates" Now I would create a procedure which firstly cleared the Date Range in the Dashboard, then I would run the Advanced filter over the Source Data, now paste that range into your Dashboard. Now push the formula into the cell next to the dates and push it to the last used cell in the range. I think it would be about 5 lines of code lets just test that


Clear Date Rng

Adv Filter

Copy Res from Adv Filter to Dashboard

Copy Formula from top of Rng to bottom


I was wrong it will take 4 lines of code to do what you want. It is dynamic, it will be consistent and it will be accurate. This is what I would do but with XL there are many ways to do the same thing.


I asked for your Dashboard so I could see the structure. I would have put the code together on the back of that.


Take care


Smallman
 
Hi, maniknandi!

I'd still stick to NARAYANK991's suggestion regarding the formula to get an unique list of items.

Regards!
 
SirJB7


The file on the website Narayan pointed to has two methods for extracting unique list of values. Using a formula and using the Advanced filter. I use the Advanced filter every day of the week over an array formula because it is much quicker to filter something than it is to write a reasonably complex forumula. It is nice that Manik has been given two options to achieve the same result.


Smallman
 
@Smallman

Hi!

Of course it's better always to have more than one option, but anyhow I tried to mean that for the intended use I prefer the formula option.

Regards!
 
Hi Manikandi


I have rearranged your data to the following construct.


Data (inputs) - Calc (Calculation) - Report (Output)


This is the way all of your models should roll. Left to right like a Western Book. I would also reconstruct your Data tab so the qualitative data (Explanatory) is on the left and qualitative data (Figures) are on the right. That means moving the SP column from I to say D.


I have finished the file and will upload it shortly.


Here is the code which does what I promised. Don’t test it on the file in your dropbox as I have altered that file to be more in line with best practice.

[pre]
Code: 
Sub MoveData()
Sheet2.Range("A3", Sheet2.Range("A65536").End(xlUp)).ClearContents
Range("Table3[[#All],[Date]]").AdvancedFilter 2, , [K1], 1
Range("K2", Range("K65536").End(xlUp)).Cut Sheet2.[A3]
Sheet2.Range("a3", Sheet2.Range("A65536").End(xlUp)).Offset(, 1).Value = "=SUMPRODUCT((Date=$A3)*(SP=$B$1)*(GP))"
Sheet2.Range("a3", Sheet2.Range("A65536").End(xlUp)).Offset(, 2).Value = "=SUMPRODUCT((Date=$A3)*(SP=$B$1)*(GREVENUE))"
End Sub

I don’t like that the file is reconstructing the formula each time it runs but you are going to have a changing list of dates and if you use the dynamic date formula the model is going to grind to a halt.  Those pesky array formulas are very heavy on memory.  Narayan would have had no way of knowing that your dates data set was 1200 lines long. It really slows things down when you have a lot of array formula.  


I also put a worksheet change event on your Report sheet and the following is the code that goes with that.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim stDate As Long
Dim endDate As Long
stDate = [C3]
endDate = [D3]

If Target.Address = "$C$3" Then
Sheet2.Range("a2", Sheet2.Range("A65536").End(xlUp)).AutoFilter 1, ">=" & stDate, xlAnd, "<=" & endDate
End If
End Sub
[/pre]
Changing B3 will change the Sales Person Data in the Charts. Just change cell C3 on your Report and the date range of the charts change too. I added a few other things. A sheet called lists and a couple of named ranges and I rearranged your output sheet to look at bit sharper. There are lots more improvements but it is getting late down under and the sand man is tapping me.


http://rapidshare.com/files/3898193334/aSales%20Analysis%20Database.xlsm


The above I have not tried before so if you can not access the file let me know. If you put your email address in a post I can send you the file also.


Take care


Smallman
 
Hi Manik


I have posted you the file. Can you continue any further posts on this forum? Also can someone tell me if the file above is accessable? It was my first use of the file sharing site RapidShare and I would love to know if people can access the file.


Thanks


Smallman
 
You must log in or register to reply here.
Back
Top