SumIFS with criteria between dates

I

Ira Hopkins

New Member
Joined
Apr 8, 2019
Messages
25
Hoping someone can help. I am trying to match a name Column A for payments between 2 date ( Quarters- ie January 1st 2019- March 31st 2019) If the criteria match I need to know how many claims and the total payment for claims for that person in the quarter- There is generally more than one claim for that person in the quarter
I have tried =SUMIFS(DOWNLOAD!F:F,DOWNLOAD!A:A,A223,DOWNLOAD!D:D,"<=1/4/2019") but when I add another date it will not work
Really hoping someone can help
[TABLE="width: 503"]
<colgroup><col span="5"><col><col></colgroup><tbody>[TR]
[TD]name[/TD]
[TD]Loc[/TD]
[TD]Practice[/TD]
[TD]No of Claims[/TD]
[TD] £ [/TD]
[TD]Payment Date[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]31-Jan-19[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]dave[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]31-Jan-18[/TD]
[TD="align: right"]29.4[/TD]
[/TR]
[TR]
[TD]brian[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]31-Jan-17[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]tom[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]31-Jan-16[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]george[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]01-Feb-18[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]tom[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]31-Jan-18[/TD]
[TD="align: right"]39.2[/TD]
[/TR]
[TR]
[TD]george[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]28-Feb-18[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]dave[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]28-Feb-18[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]colin[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]01-Mar-18[/TD]
[TD="align: right"]19.6[/TD]
[/TR]
[TR]
[TD]bob[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]02-Mar-18[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]28-Feb-18[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]dave[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]28-Feb-19[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]brian[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]01-Mar-19[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]peter[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]02-Mar-19[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]tom[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]28-Feb-18[/TD]
[TD="align: right"]19.6[/TD]
[/TR]
[TR]
[TD]george[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]28-Feb-17[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]tom[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]01-Mar-17[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]george[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]28-Jun-18[/TD]
[TD="align: right"]19.6[/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]29-Jun-18[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]dave[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]30-Jun-18[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]brian[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]28-Feb-18[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]colin[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]28-May-18[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]colin[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]28-Feb-16[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]brian[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]28-Feb-16[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]kyle[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9.8[/TD]
[TD="align: right"]29-Feb-16[/TD]
[TD="align: right"]19.6[/TD]
[/TR]
[TR]
[TD]kyle[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD] 9.80 [/TD]
[TD="align: right"]01-Mar-16[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]tom[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD] 9.80 [/TD]
[TD="align: right"]31-May-18[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]george[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD] 9.80 [/TD]
[TD="align: right"]31-May-18[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]tom[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD] 9.80 [/TD]
[TD="align: right"]31-May-17[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]george[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]2[/TD]
[TD] 9.80 [/TD]
[TD="align: right"]31-May-18[/TD]
[TD="align: right"]19.6[/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD] 9.80 [/TD]
[TD="align: right"]31-May-18[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]dave[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD] 9.80 [/TD]
[TD="align: right"]31-Jul-18[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]brian[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD] 9.80 [/TD]
[TD="align: right"]31-Jul-18[/TD]
[TD="align: right"]9.8[/TD]
[/TR]
[TR]
[TD]colin[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]2[/TD]
[TD] 10.03 [/TD]
[TD="align: right"]31-Aug-18[/TD]
[TD="align: right"]20.06[/TD]
[/TR]
[TR]
[TD]colin[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]56987[/TD]
[TD="align: right"]1[/TD]
[TD] 10.03 [/TD]
[TD="align: right"]31-Aug-18[/TD]
[TD="align: right"]10.03[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]46[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]451.49[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
According to your (unlabelled) data columns D would be number of claims, why would that be less than a date?

A is name
D is claims
F is date
G is payments

So that should be

Claims
=SUMIFS(DOWNLOAD!D:D,DOWNLOAD!A:A,A223,DOWNLOAD!F:F,"<="&DATEVALUE("31/3/2019"),DOWNLOAD!F:F,">="&DATEVALUE(1/1/2019"))

Payments
=SUMIFS(DOWNLOAD!G:G,DOWNLOAD!A:A,A223,DOWNLOAD!F:F,"<="&DATEVALUE("31/3/2019"),DOWNLOAD!F:F,">="&DATEVALUE(1/1/2019"))

and if you use a whole column, e.g. D:D you'll be including the totals too, so you'll need to change that.
 
Last edited:
Upvote 0
Special-K99 said:
According to your (unlabelled) data columns D would be number of claims, why would that be less than a date?

A is name
D is claims
F is date
G is payments

So that should be

Claims
=SUMIFS(DOWNLOAD!D:D,DOWNLOAD!A:A,A223,DOWNLOAD!F:F,"<="&DATEVALUE("31/3/2019"),DOWNLOAD!F:F,">="&DATEVALUE(1/1/2019"))

Payments
=SUMIFS(DOWNLOAD!G:G,DOWNLOAD!A:A,A223,DOWNLOAD!F:F,"<="&DATEVALUE("31/3/2019"),DOWNLOAD!F:F,">="&DATEVALUE(1/1/2019"))

and if you use a whole column, e.g. D:D you'll be including the totals too, so you'll need to change that.
Click to expand...

Hi Thank you so much for your help. I tried it but couldn't get it to work. I think it might be me not explaining myself and cant find how to attach an excel sheet to my query

What I am trying to do is on my "quarters" sheet look in my "download" sheet and look in column a for the name eg "John". Look at all the Johns in column A Downloads and if johns claims fall between eg 1/1/18 & 31/3/18 add up the number of claims. Thanks so much
 
Upvote 0
something like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Quarter[/td][td=bgcolor:#70AD47]name[/td][td=bgcolor:#70AD47]Sum[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]bob[/td][td=bgcolor:#E2EFDA]
9.8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td]colin[/td][td]
19.6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]kyle[/td][td=bgcolor:#E2EFDA]
19.6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td]peter[/td][td]
9.8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]george[/td][td=bgcolor:#E2EFDA]
29.4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td]dave[/td][td]
29.4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]john[/td][td=bgcolor:#E2EFDA]
19.6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td]tom[/td][td]
39.2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]brian[/td][td=bgcolor:#E2EFDA]
39.2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2​
[/td][td]colin[/td][td]
9.8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]tom[/td][td=bgcolor:#E2EFDA]
19.6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2​
[/td][td]george[/td][td]
29.4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]john[/td][td=bgcolor:#E2EFDA]
19.6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2​
[/td][td]dave[/td][td]
9.8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]colin[/td][td=bgcolor:#E2EFDA]
20.06​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
3​
[/td][td]brian[/td][td]
9.8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]dave[/td][td=bgcolor:#E2EFDA]
9.8​
[/td][/tr]
[/table]

or

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Quarter[/td][td=bgcolor:#70AD47]name[/td][td=bgcolor:#70AD47]SumTotal[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]bob[/td][td=bgcolor:#E2EFDA]
9.8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td]brian[/td][td]
39.2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]colin[/td][td=bgcolor:#E2EFDA]
29.4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td]dave[/td][td]
49​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]george[/td][td=bgcolor:#E2EFDA]
29.4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td]john[/td][td]
19.6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]kyle[/td][td=bgcolor:#E2EFDA]
29.4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td]peter[/td][td]
9.8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]tom[/td][td=bgcolor:#E2EFDA]
78.4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2​
[/td][td]colin[/td][td]
9.8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]dave[/td][td=bgcolor:#E2EFDA]
9.8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2​
[/td][td]george[/td][td]
49​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]john[/td][td=bgcolor:#E2EFDA]
19.6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2​
[/td][td]tom[/td][td]
19.6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]brian[/td][td=bgcolor:#E2EFDA]
9.8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
3​
[/td][td]colin[/td][td]
30.09​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]dave[/td][td=bgcolor:#E2EFDA]
9.8​
[/td][/tr]
[/table]
 
Last edited:
Upvote 0
Hi Again
Thanks again .... Table wise something like below.

Name Quarter 1 quarter 2 quarter 3
Bob 3 1 2
John 1 2 4

Its the number of claims I need to add up per person per quarter- I can do the sum of money on an end column as the items only go up with inflation so would change at the end of a quarter. The sum of money I can easily do once I know how many claims each person made per quarter

Thanks again
Tom
 
Upvote 0
like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]name[/td][td=bgcolor:#70AD47]1[/td][td=bgcolor:#70AD47]2[/td][td=bgcolor:#70AD47]3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]bob[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]brian[/td][td]
4​
[/td][td][/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]colin[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]dave[/td][td]
5​
[/td][td]
1​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]george[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]
5​
[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]john[/td][td]
2​
[/td][td]
2​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]kyle[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]peter[/td][td]
1​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]tom[/td][td=bgcolor:#E2EFDA]
8​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA][/td][/tr]
[/table]
 
Upvote 0
so...
use PowerQuery

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
    TypeDate = Table.TransformColumnTypes(Source,{{"name", type text}, {"Loc", Int64.Type}, {"Practice", Int64.Type}, {"No of Claims", Int64.Type}, {"£", type number}, {"Payment Date", type date}, {"Total", type number}}),
    InsertQ = Table.AddColumn(TypeDate, "Quarter", each Date.QuarterOfYear([Payment Date]), Int64.Type),
    Group = Table.Group(InsertQ, {"name", "Quarter"}, {{"Claims", each List.Sum([No of Claims]), type number}}),
    Prefix = Table.TransformColumns(Group, {{"Quarter", each "Qtr " & Text.From(_, "en-GB"), type text}}),
    Pivot = Table.Pivot(Table.TransformColumnTypes(Prefix, {{"Quarter", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(Prefix, {{"Quarter", type text}}, "en-GB")[Quarter]), "Quarter", "Claims", List.Sum),
    Sort = Table.Sort(Pivot,{{"name", Order.Ascending}})
in
    Sort[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]name[/td][td=bgcolor:#5B9BD5]Loc[/td][td=bgcolor:#5B9BD5]Practice[/td][td=bgcolor:#5B9BD5]No of Claims[/td][td=bgcolor:#5B9BD5]L[/td][td=bgcolor:#5B9BD5]Payment Date[/td][td=bgcolor:#5B9BD5]Total[/td][td][/td][td=bgcolor:#70AD47]name[/td][td=bgcolor:#70AD47]Qtr 1[/td][td=bgcolor:#70AD47]Qtr 2[/td][td=bgcolor:#70AD47]Qtr 3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]john[/td][td=bgcolor:#DDEBF7]
67​
[/td][td=bgcolor:#DDEBF7]
56987​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td=bgcolor:#DDEBF7]
31-Jan-19​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td][/td][td=bgcolor:#E2EFDA]bob[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]dave[/td][td]
67​
[/td][td]
56987​
[/td][td]
3​
[/td][td]
9.8​
[/td][td]
31-Jan-18​
[/td][td]
29.4​
[/td][td][/td][td]brian[/td][td]
4​
[/td][td][/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]brian[/td][td=bgcolor:#DDEBF7]
67​
[/td][td=bgcolor:#DDEBF7]
56987​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td=bgcolor:#DDEBF7]
31-Jan-17​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td][/td][td=bgcolor:#E2EFDA]colin[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]tom[/td][td]
67​
[/td][td]
56987​
[/td][td]
1​
[/td][td]
9.8​
[/td][td]
31-Jan-16​
[/td][td]
9.8​
[/td][td][/td][td]dave[/td][td]
5​
[/td][td]
1​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]george[/td][td=bgcolor:#DDEBF7]
67​
[/td][td=bgcolor:#DDEBF7]
56987​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td=bgcolor:#DDEBF7]
01-Feb-18​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td][/td][td=bgcolor:#E2EFDA]george[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]
5​
[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]tom[/td][td]
67​
[/td][td]
56987​
[/td][td]
4​
[/td][td]
9.8​
[/td][td]
31-Jan-18​
[/td][td]
39.2​
[/td][td][/td][td]john[/td][td]
2​
[/td][td]
2​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]george[/td][td=bgcolor:#DDEBF7]
67​
[/td][td=bgcolor:#DDEBF7]
56987​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td=bgcolor:#DDEBF7]
28-Feb-18​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td][/td][td=bgcolor:#E2EFDA]kyle[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]dave[/td][td]
67​
[/td][td]
56987​
[/td][td]
1​
[/td][td]
9.8​
[/td][td]
28-Feb-18​
[/td][td]
9.8​
[/td][td][/td][td]peter[/td][td]
1​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]colin[/td][td=bgcolor:#DDEBF7]
67​
[/td][td=bgcolor:#DDEBF7]
56987​
[/td][td=bgcolor:#DDEBF7]
2​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td=bgcolor:#DDEBF7]
01-Mar-18​
[/td][td=bgcolor:#DDEBF7]
19.6​
[/td][td][/td][td=bgcolor:#E2EFDA]tom[/td][td=bgcolor:#E2EFDA]
8​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]bob[/td][td]
67​
[/td][td]
56987​
[/td][td]
1​
[/td][td]
9.8​
[/td][td]
02-Mar-18​
[/td][td]
9.8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]john[/td][td=bgcolor:#DDEBF7]
67​
[/td][td=bgcolor:#DDEBF7]
56987​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td=bgcolor:#DDEBF7]
28-Feb-18​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]dave[/td][td]
67​
[/td][td]
56987​
[/td][td]
1​
[/td][td]
9.8​
[/td][td]
28-Feb-19​
[/td][td]
9.8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]brian[/td][td=bgcolor:#DDEBF7]
67​
[/td][td=bgcolor:#DDEBF7]
56987​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td=bgcolor:#DDEBF7]
01-Mar-19​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]peter[/td][td]
67​
[/td][td]
56987​
[/td][td]
1​
[/td][td]
9.8​
[/td][td]
02-Mar-19​
[/td][td]
9.8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]tom[/td][td=bgcolor:#DDEBF7]
67​
[/td][td=bgcolor:#DDEBF7]
56987​
[/td][td=bgcolor:#DDEBF7]
2​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td=bgcolor:#DDEBF7]
28-Feb-18​
[/td][td=bgcolor:#DDEBF7]
19.6​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]george[/td][td]
67​
[/td][td]
56987​
[/td][td]
1​
[/td][td]
9.8​
[/td][td]
28-Feb-17​
[/td][td]
9.8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]tom[/td][td=bgcolor:#DDEBF7]
67​
[/td][td=bgcolor:#DDEBF7]
56987​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td=bgcolor:#DDEBF7]
01-Mar-17​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]george[/td][td]
67​
[/td][td]
56987​
[/td][td]
2​
[/td][td]
9.8​
[/td][td]
28-Jun-18​
[/td][td]
19.6​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]john[/td][td=bgcolor:#DDEBF7]
67​
[/td][td=bgcolor:#DDEBF7]
56987​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td=bgcolor:#DDEBF7]
29-Jun-18​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]dave[/td][td]
67​
[/td][td]
56987​
[/td][td]
1​
[/td][td]
9.8​
[/td][td]
30-Jun-18​
[/td][td]
9.8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]brian[/td][td=bgcolor:#DDEBF7]
67​
[/td][td=bgcolor:#DDEBF7]
56987​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td=bgcolor:#DDEBF7]
28-Feb-18​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]colin[/td][td]
67​
[/td][td]
56987​
[/td][td]
1​
[/td][td]
9.8​
[/td][td]
28-May-18​
[/td][td]
9.8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]colin[/td][td=bgcolor:#DDEBF7]
67​
[/td][td=bgcolor:#DDEBF7]
56987​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td=bgcolor:#DDEBF7]
28-Feb-16​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]brian[/td][td]
67​
[/td][td]
56987​
[/td][td]
1​
[/td][td]
9.8​
[/td][td]
28-Feb-16​
[/td][td]
9.8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]kyle[/td][td=bgcolor:#DDEBF7]
67​
[/td][td=bgcolor:#DDEBF7]
56987​
[/td][td=bgcolor:#DDEBF7]
2​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td=bgcolor:#DDEBF7]
29-Feb-16​
[/td][td=bgcolor:#DDEBF7]
19.6​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]kyle[/td][td]
67​
[/td][td]
56987​
[/td][td]
1​
[/td][td]
9.8​
[/td][td]
01-Mar-16​
[/td][td]
9.8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]tom[/td][td=bgcolor:#DDEBF7]
67​
[/td][td=bgcolor:#DDEBF7]
56987​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td=bgcolor:#DDEBF7]
31-May-18​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]george[/td][td]
67​
[/td][td]
56987​
[/td][td]
1​
[/td][td]
9.8​
[/td][td]
31-May-18​
[/td][td]
9.8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]tom[/td][td=bgcolor:#DDEBF7]
67​
[/td][td=bgcolor:#DDEBF7]
56987​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td=bgcolor:#DDEBF7]
31-May-17​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]george[/td][td]
67​
[/td][td]
56987​
[/td][td]
2​
[/td][td]
9.8​
[/td][td]
31-May-18​
[/td][td]
19.6​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]john[/td][td=bgcolor:#DDEBF7]
67​
[/td][td=bgcolor:#DDEBF7]
56987​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td=bgcolor:#DDEBF7]
31-May-18​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]dave[/td][td]
67​
[/td][td]
56987​
[/td][td]
1​
[/td][td]
9.8​
[/td][td]
31-Jul-18​
[/td][td]
9.8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]brian[/td][td=bgcolor:#DDEBF7]
67​
[/td][td=bgcolor:#DDEBF7]
56987​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td=bgcolor:#DDEBF7]
31-Jul-18​
[/td][td=bgcolor:#DDEBF7]
9.8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]colin[/td][td]
67​
[/td][td]
56987​
[/td][td]
2​
[/td][td]
10.03​
[/td][td]
31-Aug-18​
[/td][td]
20.06​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]colin[/td][td=bgcolor:#DDEBF7]
67​
[/td][td=bgcolor:#DDEBF7]
56987​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
10.03​
[/td][td=bgcolor:#DDEBF7]
31-Aug-18​
[/td][td=bgcolor:#DDEBF7]
10.03​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
 
Upvote 0
Im not familiar with power query sorry. Will try and learn though. In the meantime is there a formula that i could use? I really do appreciatte your help. Thankyou so much
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Sumif/Sumproduct with non-continuous row
Replies
2
Views
87
Goddess
G
J
  • Question Question
auto filling cells based on preset format
Replies
1
Views
35
HighAndWilder
H
Vintage79
  • Solved
Count consecutive days and gaps between days
Replies
2
Views
218
Vintage79
Vintage79
T
  • Question Question
RAINFALL CESSATION DATE USING INDEX
Replies
1
Views
245
Thlama
T
C
  • Question Question
Sumifs with Date Criteria
Replies
2
Views
56
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top