1

I work for a bookmaker and have the following problem.

I would like to find the sum of total stake for particular groups of clients for particular sports, between certain dates.

Here is the code i have at the moment which works well for 1 client code :

=SUMIFS(Databank!$G:$G,Databank!$C:$C,Template!$A$10,Databank!$J:$J,Template!$E24,Databank!B:B,">="&Template!$B$3,Databank!B:B,"<="&Template!$B$4)

Reference :

  • Databank G is stake
  • Databank C is client code
  • Template A10 is the cell i'm referring to with a client code in
  • Databank J is Sport
  • Template E24 is the sport i would like summarising
  • Databank B is date
  • Template B3 and B4 are to and from dates.

Currently this formula tells me the total stake of the client in A10 for the sport in E24 between the dates in B3 and B4.

I would like to amend the formula to be able to group more clients together from cells A10 - A100, A101 etc

BattleTested_закалённый в бою
  • 4,037
  • 5
  • 26
  • 47
Rob Stephenson
  • 23
  • 4
  • Now, you got a summation for `A10`. You want another one sum for `A11`, and another for `A12`, and so on. don't you? – PaichengWu Jul 26 '18 at 15:27
  • Hi, yes i get a summation for A10, i would like to include the client codes in A11, A12 etc into the figure. So I end up with the total stake for X amount of clients in one cell – Rob Stephenson Jul 26 '18 at 15:29
  • I would like to recommend [this](https://stackoverflow.com/questions/4939537/how-to-loop-in-excel-without-vba-or-macros) solution. | Simply.. add another sheet, collect the result, retrieve it using ` = ` . ( : – p._phidot_ Jul 26 '18 at 16:08
  • @p._phidot_I have 22 different sports and a six groups of clients to make up, groups ranging from 3 people to 100 people. I'm not sure if this solution will help – Rob Stephenson Jul 26 '18 at 16:33

2 Answers2

0

For this kind of number crunching, you really should be using a PivotTable, and some Slicers to let you quickly and easily select the variables that you want the PivotTable to show the aggregated totals for.

See my answer at VBA to copy data if multiple criteria are met on how to insert a PivotTable. In your case, you need to drag the Stake field into the Values area of the PivotTable, and put the other fields into the Filters area.

Give Google a spin in regards to how to add slicers. Easy as pie.

jeffreyweir
  • 4,668
  • 1
  • 16
  • 27
0

Find two empty columns, say x and y.

In x10, write

=SUMIFS(Databank!$G:$G
,Databank!$C:$C,Template!$A10
,Databank!$J:$J,Template!$E24
,Databank!B:B,">="&Template!$B$3
,Databank!B:B,"<="&Template!$B$4)

which replace $A$10 of your formula with $A10.

Drag x10 down to x11, x12 and so on.

In y1, write =SUM(X:X) will return the summation.

PaichengWu
  • 2,649
  • 1
  • 14
  • 28