SumIFS with criteria between dates

[Ira Hopkins]

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

<colgroup><col span="5"><col><col></colgroup><tbody>
</tbody>

 

[Joe4]

Your data sample has data from 4 different year (2016-2019).
So when you say Quarter 1, do you mean for one specific year, or do you want to count the records for quarter 1 for ALL 4 years, totalled up?

 

[Ira Hopkins]

Hi Joel it would be for each specific year. Thanks

 

[Joe4]

Then you should be able to use formulas like:
Qtr 1 2018: =COUNTIFS($A:$A,$I2,$F:$F,">=" & DATE(2018,1,1),$F:$F,"<=" & DATE(2018,3,31))
Qtr 2 2018: =COUNTIFS($A:$A,$I2,$F:$F,">=" & DATE(2018,4,1),$F:$F,"<=" & DATE(2018,6,30))
etc.

Note that you may need to change the column references to match what columns your data actually is in.

 

[Ira Hopkins]

OMG Joe - Thank you SO SO much- had a play with the formula and it seems to be working- You have really saved me so much copy and paste/filter time- its amazing!!! Once again thank you so much

 

[Joe4]

You are welcome.
Glad we were able to help you out!

 

[Ira Hopkins]

Hi Joe - I do have 1 problem - The column I am trying to add up sometimes has more than 1 claim - ie 2 instead of one. I tried changing the COUNTIFS to SUMIFS and its saying too few arguments. It works great on a COUNTIF- Is there a way to SUM ? Thanks SO much .

 

[Joe4]

COUNTIFS will count the number of records meeting the Criteria.
SUMIFS will sum up a specificed column for the records meeting the Criteria.
So, SUMIFS is similar to COUNTIFS, but it has an extra column, as you need to indicate which column it is that you want to sum. That is added as the first argument.
(A SUMIFS formula would look almost identical to the COUNTIFS formula, except for an extra argument in the first position).

See here a detailed explanation and exampleL https://exceljet.net/excel-functions/excel-sumifs-function

 

[Ira Hopkins]

Think Ive got it THANK YOU SO MUCH Joe !!!! =SUMIFS($F:$F,$A:$A,"John",$D:$D,">=" & DATE(2018,1,1),$D:$D,"<=" & DATE(2018,3,31)) - And it seems to have worked - Thanks again !