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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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

Vintage79
  • Solved
Count consecutive days and gaps between days
Replies
2
Views
216
Vintage79
Vintage79
T
  • Question Question
RAINFALL CESSATION DATE USING INDEX
Replies
1
Views
237
Thlama
T
E
  • Solved
Formula not working
Replies
2
Views
316
esmarques
E
staticfluids
  • Solved
Dynamic Array Formula (FILTER) But Show the Other Criteria with Blank
Replies
4
Views
497
staticfluids
staticfluids
skyh3ck
  • Question Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
546
skyh3ck
skyh3ck

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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