SumIFS with criteria between dates

Ira Hopkins · Apr 8, 2019

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

name	Loc	Practice	No of Claims	£	Payment Date	Total
john	67	56987	1	9.8	31-Jan-19	9.8
dave	67	56987	3	9.8	31-Jan-18	29.4
brian	67	56987	1	9.8	31-Jan-17	9.8
tom	67	56987	1	9.8	31-Jan-16	9.8
george	67	56987	1	9.8	01-Feb-18	9.8
tom	67	56987	4	9.8	31-Jan-18	39.2
george	67	56987	1	9.8	28-Feb-18	9.8
dave	67	56987	1	9.8	28-Feb-18	9.8
colin	67	56987	2	9.8	01-Mar-18	19.6
bob	67	56987	1	9.8	02-Mar-18	9.8
john	67	56987	1	9.8	28-Feb-18	9.8
dave	67	56987	1	9.8	28-Feb-19	9.8
brian	67	56987	1	9.8	01-Mar-19	9.8
peter	67	56987	1	9.8	02-Mar-19	9.8
tom	67	56987	2	9.8	28-Feb-18	19.6
george	67	56987	1	9.8	28-Feb-17	9.8
tom	67	56987	1	9.8	01-Mar-17	9.8
george	67	56987	2	9.8	28-Jun-18	19.6
john	67	56987	1	9.8	29-Jun-18	9.8
dave	67	56987	1	9.8	30-Jun-18	9.8
brian	67	56987	1	9.8	28-Feb-18	9.8
colin	67	56987	1	9.8	28-May-18	9.8
colin	67	56987	1	9.8	28-Feb-16	9.8
brian	67	56987	1	9.8	28-Feb-16	9.8
kyle	67	56987	2	9.8	29-Feb-16	19.6
kyle	67	56987	1	9.80	01-Mar-16	9.8
tom	67	56987	1	9.80	31-May-18	9.8
george	67	56987	1	9.80	31-May-18	9.8
tom	67	56987	1	9.80	31-May-17	9.8
george	67	56987	2	9.80	31-May-18	19.6
john	67	56987	1	9.80	31-May-18	9.8
dave	67	56987	1	9.80	31-Jul-18	9.8
brian	67	56987	1	9.80	31-Jul-18	9.8
colin	67	56987	2	10.03	31-Aug-18	20.06
colin	67	56987	1	10.03	31-Aug-18	10.03
			46			451.49

Special-K99 · Apr 8, 2019

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.

Ira Hopkins · Apr 8, 2019

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.

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

Special-K99 · Apr 8, 2019

FYI

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

sandy666 · Apr 8, 2019

something like this?

Quarter	name	Sum
1	bob	9.8
1	colin	19.6
1	kyle	19.6
1	peter	9.8
1	george	29.4
1	dave	29.4
1	john	19.6
1	tom	39.2
1	brian	39.2
2	colin	9.8
2	tom	19.6
2	george	29.4
2	john	19.6
2	dave	9.8
3	colin	20.06
3	brian	9.8
3	dave	9.8

or

Quarter	name	SumTotal
1	bob	9.8
1	brian	39.2
1	colin	29.4
1	dave	49
1	george	29.4
1	john	19.6
1	kyle	29.4
1	peter	9.8
1	tom	78.4
2	colin	9.8
2	dave	9.8
2	george	49
2	john	19.6
2	tom	19.6
3	brian	9.8
3	colin	30.09
3	dave	9.8

Ira Hopkins · Apr 8, 2019

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

sandy666 · Apr 8, 2019

like this?

name	1	2	3
bob	1
brian	4		1
colin	3	1	3
dave	5	1	1
george	3	5
john	2	2
kyle	3
peter	1
tom	8	2

Ira Hopkins · Apr 8, 2019

Yes thats it.... Thanks

sandy666 · Apr 8, 2019

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]

name	Loc	Practice	No of Claims	L	Payment Date	Total	name	Qtr 1	Qtr 2	Qtr 3
john	67	56987	1	9.8	31-Jan-19	9.8	bob	1
dave	67	56987	3	9.8	31-Jan-18	29.4	brian	4		1
brian	67	56987	1	9.8	31-Jan-17	9.8	colin	3	1	3
tom	67	56987	1	9.8	31-Jan-16	9.8	dave	5	1	1
george	67	56987	1	9.8	01-Feb-18	9.8	george	3	5
tom	67	56987	4	9.8	31-Jan-18	39.2	john	2	2
george	67	56987	1	9.8	28-Feb-18	9.8	kyle	3
dave	67	56987	1	9.8	28-Feb-18	9.8	peter	1
colin	67	56987	2	9.8	01-Mar-18	19.6	tom	8	2
bob	67	56987	1	9.8	02-Mar-18	9.8
john	67	56987	1	9.8	28-Feb-18	9.8
dave	67	56987	1	9.8	28-Feb-19	9.8
brian	67	56987	1	9.8	01-Mar-19	9.8
peter	67	56987	1	9.8	02-Mar-19	9.8
tom	67	56987	2	9.8	28-Feb-18	19.6
george	67	56987	1	9.8	28-Feb-17	9.8
tom	67	56987	1	9.8	01-Mar-17	9.8
george	67	56987	2	9.8	28-Jun-18	19.6
john	67	56987	1	9.8	29-Jun-18	9.8
dave	67	56987	1	9.8	30-Jun-18	9.8
brian	67	56987	1	9.8	28-Feb-18	9.8
colin	67	56987	1	9.8	28-May-18	9.8
colin	67	56987	1	9.8	28-Feb-16	9.8
brian	67	56987	1	9.8	28-Feb-16	9.8
kyle	67	56987	2	9.8	29-Feb-16	19.6
kyle	67	56987	1	9.8	01-Mar-16	9.8
tom	67	56987	1	9.8	31-May-18	9.8
george	67	56987	1	9.8	31-May-18	9.8
tom	67	56987	1	9.8	31-May-17	9.8
george	67	56987	2	9.8	31-May-18	19.6
john	67	56987	1	9.8	31-May-18	9.8
dave	67	56987	1	9.8	31-Jul-18	9.8
brian	67	56987	1	9.8	31-Jul-18	9.8
colin	67	56987	2	10.03	31-Aug-18	20.06
colin	67	56987	1	10.03	31-Aug-18	10.03

Ira Hopkins · Apr 8, 2019

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

SumIFS with criteria between dates

Ira Hopkins

New Member

Excel Facts

Special-K99

Well-known Member

Ira Hopkins

New Member

Special-K99

Well-known Member

sandy666

Banned - Rules violations

Ira Hopkins

New Member

sandy666

Banned - Rules violations

Ira Hopkins

New Member

sandy666

Banned - Rules violations

Ira Hopkins

New Member

Similar threads

Forum statistics

Share this page

SumIFS with criteria between dates

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Banned - Rules violations

New Member

Banned - Rules violations

New Member

Banned - Rules violations

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock