SUMIFS - reference multiple cells

Question

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

Answer 1

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.

Answer 2

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.