SumIFS with criteria between dates

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]
 
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?
 
Upvote 0

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
You are welcome.
Glad we were able to help you out!
:)
 
Upvote 0
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 .
 
Upvote 0
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
 
Upvote 0
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 !
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

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