Unique list from Table column and sum 2nd column values

drewberts · Sep 1, 2017

I'm not sure of the best way to work this but equally I think it must be a common requirement so if someone could simply tell me what it is I'm trying to achieve i can google away and find the solution (I think) - I'm not even sure that makes sense!

here goes

I have a table which lists all my clients (and includes duplicates as each on relates to a fee charged at any one point) plus a date. Therefore I can have multiple entries for the same client, each with an individual fee charged on a particular date.

On a separate sheet I want to create a unique list of these clients in one column (which I can do) but also the sum of all the values for that client in a second column. The list then needs sorting by the sum column and i'd also like to use a date filter (2 cells with a start date and an end date) to only show those clients (and the sum value) which have dates between the 2 dates in the filter cells.

Hope that makes sense.

Aladin Akyurek · Sep 1, 2017

Try to post a small version of the table that you have along with the desired results.

drewberts · Sep 1, 2017

So on sheet 1 I have:-

On sheet 2 I'm looking for a dynamic list to be created whenever say F9 is pressed which summarises the info on sheet 1 in Fee order based on the start and end date as below:-

Aladin Akyurek · Sep 1, 2017

It would be much easier on a would-be helper when the sample is Excel readable, though.

Let A1:C20 of Sheet1 house the data, the headers included.

Let A2:B2 of Sheet2 house the start and end dates (which appears to indicate a certain month/year.

In A3 of Sheet2 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(Sheet1!$A$2:$A$20=""),IF(Sheet1!$B$2:$B$20-DAY(Sheet1!$B$2:$B$20)+1=A2,MATCH(Sheet1!$A$2:$A$20,Sheet1!$A$2:$A$20,0))),ROW(Sheet1!$A$2:$A$20)-ROW(Sheet1!$A$2)+1),1))

In A5 of Sheet2 control+shift+enter, not just enter, and copy down:

=IF($B5="","",INDEX(Sheet1!$A$2:$A$8,SMALL(IF(SUMIFS(Sheet1!$C$2:$C$20,Sheet1!$A$2:$A$20,IF(FREQUENCY(IF(1-(Sheet1!$A$2:$A$20=""),IF(Sheet1!$B$2:$B$20-DAY(Sheet1!$B$2:$B$20)+1=$A$2,MATCH(Sheet1!$A$2:$A$20,Sheet1!$A$2:$A$20,0))),ROW(Sheet1!$A$2:$A$20)-ROW(Sheet1!$A$2)+1),Sheet1!$A$2:$A$20),Sheet1!$B$2:$B$20,">="&$A$2,Sheet1!$B$2:$B$20,"<="&$B$2)=$B5,ROW(Sheet1!$A$2:$A$20)-ROW(Sheet1!$A$2)+1),COUNTIFS($B$5:B5,B5))))

In B5 of Sheet2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($B$5:B5)>$A$3,"",LARGE(SUMIFS(Sheet1!$C$2:$C$20,Sheet1!$A$2:$A$20,IF(FREQUENCY(IF(1-(Sheet1!$A$2:$A$20=""),IF(Sheet1!$B$2:$B$20-DAY(Sheet1!$B$2:$B$20)+1=$A$2,MATCH(Sheet1!$A$2:$A$20,Sheet1!$A$2:$A$20,0))),ROW(Sheet1!$A$2:$A$20)-ROW(Sheet1!$A$2)+1),Sheet1!$A$2:$A$20),Sheet1!$B$2:$B$20,">="&$A$2,Sheet1!$B$2:$B$20,"<="&$B$2),ROWS($B$5:B5)))

drewberts · Sep 1, 2017

Apologies - The sheet is part of a much bigger workbook with links etc all over the place so i thought it may be simpler just to post a couple of images - point noted though.

Thanks for your response, I'll have a work through.

drewberts · Sep 5, 2017

Thanks Aladin for your help so far - i've worked through and this is what I have...

All seems to work ok except occasionally there is an entry with a fee of 0 (the reasons for this don't really matter), just it returns an #N/A error in the client name - what amendments do i need to make to handle any clients with a fee of 0 against them?

The formula seems to work for any given month but if I put in 01/09/16 and 31/08/17 (our FY) I get what seems like a random amount back (e.g. it calculates the 1st four then misses one out completely - Will it only work for a month date range?

Happy to send you across the sheet if it helps but i'll need to PM it as there is sensitive info within it.

Aladin Akyurek · Sep 6, 2017

drewberts said:
Thanks Aladin for your help so far - i've worked through and this is what I have...

Good job.

All seems to work ok except occasionally there is an entry with a fee of 0 (the reasons for this don't really matter), just it returns an #N/A error in the client name - what amendments do i need to make to handle any clients with a fee of 0 against them?

I did not expect a 0 fee. Its presence forces us to contain the othwerwise absolutely correct behavior of the frequency function. Here IFERROR comes to rescue.

The formula seems to work for any given month but if I put in 01/09/16 and 31/08/17 (our FY) I get what seems like a random amount back (e.g. it calculates the 1st four then misses one out completely - Will it only work for a month date range?

I should have not have confined the set up to a month date range. No worry. It's easy to resolve.

In what follows I'll use named ranges to make the formulas more readable.

Happy to send you across the sheet if it helps but i'll need to PM it as there is sensitive info within it.

Thanks. I don't think that's necessary.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A

[/td][td]

B

[/td][td]

C

[/td][/tr][tr][td]

1

[/td][td]client[/td][td]date[/td][td]fee[/td][/tr]
[tr][td]

2

[/td][td]john[/td][td]

8/1/2016

[/td][td]

0

[/td][/tr]
[tr][td]

3

[/td][td]dave[/td][td]

8/5/2016

[/td][td]

600

[/td][/tr]
[tr][td]

4

[/td][td]fred[/td][td]

8/8/2016

[/td][td]

750

[/td][/tr]
[tr][td]

5

[/td][td]dave[/td][td]

8/14/2016

[/td][td]

250

[/td][/tr]
[tr][td]

6

[/td][td]steve[/td][td]

9/1/2016

[/td][td]

400

[/td][/tr]
[tr][td]

7

[/td][td]steve[/td][td]

9/2/2016

[/td][td]

50

[/td][/tr]
[tr][td]

8

[/td][td]colin[/td][td]

10/3/2016

[/td][td]

300

[/td][/tr]
[tr][td]

9