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

Trying to get a result based on persons name, and variation of object sold

Afternoon all

I've attached a quick sheet for reference.

I have a list of sales people, the method the order was placed, qty. of orders, and value of orders

I'm trying to summarise how many of each order type, each sales person has sold, and the value for each order type each person has sold

Many thanks

Brian
 

Attachments

  • sales person summary.xlsx
    9.5 KB · Views: 9
Try,

In I3, copied across to J3 and all copied down :

=IF($H3="","",SUMPRODUCT(($A$1:$A$100=LOOKUP(1,-FIND(" ",$H$2:$H3),SUBSTITUTE($H$2:$H3," ","")))*($C$1:$C$100=$H3),D$1:D$100))

Regards
Bosco
 

Attachments

  • sales person(2).xlsx
    13.3 KB · Views: 9
Hi Chirayu

Thank you for the sample sheet

I think I've done something wrong, as you'll see my table next to yours, when I was trying to duplicate it. (sheet attached)

The table isn't spread over as many columns as yours.

Could you let me know what I did wrong please.

Regards

Brian
 

Attachments

  • sales person summary rev1.xlsx
    17.6 KB · Views: 5
Try,

In I3, copied across to J3 and all copied down :

=IF($H3="","",SUMPRODUCT(($A$1:$A$100=LOOKUP(1,-FIND(" ",$H$2:$H3),SUBSTITUTE($H$2:$H3," ","")))*($C$1:$C$100=$H3),D$1:D$100))

Regards
Bosco
Hi Bosco,

Thanks for getting back to me.

That seems to work just fine, so thank you so much for your help

Really is much appreciated

Regards

Brian
 
To duplicate my Pivot read below and see attached
1) Put SalesPerson and OrderType in Rows field
2) Put Quantity and Value in Values field
3a) Then to show blank rows click on the down arrow of OrderType and click on "Field Settings"
3b) In the "Layout and Print" tab of the popup tick the "Show items with no data" option and click on OK
 

Attachments

  • Untitled.png
    Untitled.png
    50.6 KB · Views: 11
Hi Chirayu

I tried your method, but I think I got the whole thing wrong.

The pops up referred to, didn't pop up for me.

I've attached the actual file so you can see what I'm trying to achieve

You'll see the yellow highlighted columns is what I'm trying to achieve, with my pivot tables to the right.

One pivot refers to data in the "quotes" sheet, and the other pivot refers to data in the "sales" sheet

Any pointers/assistance would be greatly appreciated

Regards

Brian


To duplicate my Pivot read below and see attached
1) Put SalesPerson and OrderType in Rows field
2) Put Quantity and Value in Values field
3a) Then to show blank rows click on the down arrow of OrderType and click on "Field Settings"
3b) In the "Layout and Print" tab of the popup tick the "Show items with no data" option and click on OK
 

Attachments

  • week ending 17th March 19.xlsx
    322.5 KB · Views: 4
Hi Chirayu,

That looks awesome.

Is there a way in columns L & M I could remove "total quotes" and 279, as the grand total is adding total quotes and total doors values.

Also in columns P to R, is there a way to remove "total orders", 294 and £122652.42; as this is adding to the values from total doors

Hopefully it's just something simple, but I didn't want to mess up all your great work so far

Regards

Brian
 
Drag "Type" to the "Filters" section and select only "Quote" for pivot on left. Choose "Orders" for Pivot on right
 
This picks up on a solution offered by @bosco_yip but approaches the problem with radically different aims. First, I set out the eliminate all direct references to cells because I see cell location as irrelevant to any problem. Where possible I also seek an array solution in preference to a filled range. At present, multi-cell array solutions have a poor reputation due to their inflexibility but I hope the coming of dynamic arrays will reverse that perception.

59303

Postscript. I have just downloaded this onto another computer with Office 365 insider. Changing the index 'k' to
= SEQUENCE(ROWS(ChHdr))
is a relief. The index never really did depend upon the sheet row in any way.

The other thing I did was remove both CSE array formulae with a spilt arrays entered in the first cell. It works like a charm and will grow gracefully as the number of staff data blocks changes.
 

Attachments

  • sales person(PB).xlsx
    15.4 KB · Views: 5
Last edited:
I think it is the pivot table that has prevailed in the task of summarising the quotes and sales data; perhaps not that much of a surprise because the accumulation and categorisation of lists is the task for which pivot tables were purpose-written, I believe at considerable expense.

By comparison, solutions based upon formulas require the user to invest time and effort developing custom solutions covering specific aspects of PT functionality. Despite that I find it interesting to examine the impact modern dynamic arrays might have applied to this kind of problem. Typically it is the UNIQUE and SORT functions that are capable of replicating the row and column heading of the PT with SUMIFS and COUNTIFS aggregating the data fields.

In the 'subproblem' that I looked at it was the predefined pattern of row heading that was a challenge; in particular identifying the Name appropriate to a row when it appears as a block heading rather than being replicated on every row. The challenge was to create a formula that would return the name, given the record number. That can be achieve using LOOKUP to return the highest header record number lower than the current row.

I believe this can be made more efficient by filtering out anything other than the block headings and their associated record numbers, as if for a contents page. To achieve that I first combined the two columns into a single two-column array using IF and then applied a filter
= FILTER( IF( {0,1}, NameHdr, k ), LEN(NameHdr) )
to create a table '§Name' [this is a legal name despite the '§']
59317
The rest of the solution is largely unaltered with the minor exception that the Sales Person's name is now looked up using the array form of LOOKUP
= LOOKUP( k, §Name )

I have attached a copy of the workbook but it will only function within EXCEL 365 insider with the DA functionality.
 

Attachments

  • sales person(contents).xlsx
    22.3 KB · Views: 7
Back
Top