Counting in date range with 2 criteria

Helix242

Board Regular
Joined
Jun 28, 2016
Messages
76
Evening everyone,

I hope you may be able to help. knowledge of Excel is okay, however I am struggling with this one and any help would be appreciated!

I need to calculate the total of payments paid from a certain person based on a date range for over 300 people. For example in the following rows/columns;

Mr Smith - 1/4/18 - £123
Mr Hyde - 20/5/18 - £99
Mr Smith - 19/9/18 - £102
Mr Red - 5/12/18 - £32
Mr Hyde - 3/7/19 - £232
Mr Smith - 1/10/19 - £232
And so on....

Date range from X
Date range to Y

I know how to find and calculate the amounts for say Mr Smith etc with the SUMIFS formula, however I am unsure how to capture this information also based between 2 dates. For instance if I only want to find out how much Mr Smith has paid between X (1/4/18) and Y (1/8/19) how would this look in a formula and would I have to use an array?

Please bear in mind my data is not in columns next to each other like the example above and it spans over 300 rows.

Many thanks in advance!
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,950
Office Version
  1. 365
Platform
  1. Windows
If you know how to use SUMIFS to get the total between two dates, you have already done the hard part. The other part is trivial.
You have an unlimited number of set of conditions with SUMIFS (actually, there is a limit, but you won't hit it).
See: https://exceljet.net/excel-functions/excel-sumifs-function
Note how range/criteria combinations can continue on past 2.
 

Helix242

Board Regular
Joined
Jun 28, 2016
Messages
76

ADVERTISEMENT

If you know how to use SUMIFS to get the total between two dates, you have already done the hard part. The other part is trivial.
You have an unlimited number of set of conditions with SUMIFS (actually, there is a limit, but you won't hit it).
See: https://exceljet.net/excel-functions/excel-sumifs-function
Note how range/criteria combinations can continue on past 2.

Thank you for your help so far Joe, although I am finding this a little trickier than I thought it would be. After reviewing the documentation my formula looks like this;

Code:
=SUMIFS(W9:W99999,V9:V99999,E9:E99999,E9:E99999,">="&B12,E9:E99999,"<="&B13)

V=The name
W= The amount
E = The date
B= The date range

I know this isn't exactly correct (I'm sure your laughing right now!), but if you could point me in the right direction it would be appreciated. :)

Many thanks
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,950
Office Version
  1. 365
Platform
  1. Windows
You have issues with your Criteria values. They cannot be whole ranges. They should be a single value or single cell.
If you have 10 different people, and want to return total for "Bill", the criteria associated with the name range would need to be "Bill" or a cell with the word "Bill" in it.

Think about it. You cannot return separate totals for 10 different people in one cell! You would need 10 different cells (one for each person).
They example in my last post shows this.
 

Helix242

Board Regular
Joined
Jun 28, 2016
Messages
76

ADVERTISEMENT

You have issues with your Criteria values. They cannot be whole ranges. They should be a single value or single cell.
If you have 10 different people, and want to return total for "Bill", the criteria associated with the name range would need to be "Bill" or a cell with the word "Bill" in it.

Think about it. You cannot return separate totals for 10 different people in one cell! You would need 10 different cells (one for each person).
They example in my last post shows this.

Hi Joe,

I think we may be getting crossed wires. As per my original example (below) I want to find out the amounts paid by Mr Smith between 1/4/18 and 1/8/19. In this instance it will be £123 and £102. The last one listed for £232 will not be there as it falls outside the date range. the result would come back to the one cell.

Yes, I agree I would need a cell for Mr Smith, Mr Hyde etc (there are about 11 names in total), but I am struggling to get even just 1 formula correct.

Can you help? Thank you! :)

Mr Smith - 1/4/18 - £123
Mr Hyde - 20/5/18 - £99
Mr Smith - 19/9/18 - £102
Mr Red - 5/12/18 - £32
Mr Hyde - 3/7/19 - £232
Mr Smith - 1/10/19 - £232
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,950
Office Version
  1. 365
Platform
  1. Windows
I think we may be getting crossed wires.
No, I understand perfectly what you are wanting to do. I see that you are having issues taking what you need from each of the links I provided to get what you need (it is all there, just needs to be combined).

So, let's work with the simple example you provided. Let's say the data you posted above is found in the range A2:C7. And let's say that you have the following "criteria" in the following cells:
E2: Mr Smith
E3: 1/4/18
E4: 1/8/19

So, then the formula to return the total amount for Mr Smith would look like:
Code:
=SUMIFS([COLOR=#0000ff]C2:C7[/COLOR],[COLOR=#ff0000]A2:A7,E2[/COLOR],[COLOR=#008000]B2:B7,">=" &E3[/COLOR],[COLOR=#ff8c00]B2:B7,"<="&E4[/COLOR])
Blue: range you want to sum
Red: name range and name criteria
Green: date range and lower date criteria
Orange: date range and upper date criteria

Note if this is a formula you want to copy down a column, you will want to make some of your ranges absolute references to lock them down, i.e. $C$2:$C$7
(see here: https://www.excel-easy.com/functions/cell-references.html)
 
Last edited:

Helix242

Board Regular
Joined
Jun 28, 2016
Messages
76
No, I understand perfectly what you are wanting to do. I see that you are having issues taking what you need from each of the links I provided to get what you need (it is all there, just needs to be combined).

So, let's work with the simple example you provided. Let's say the data you posted above is found in the range A2:C7. And let's say that you have the following "criteria" in the following cells:
E2: Mr Smith
E3: 1/4/18
E4: 1/8/19

So, then the formula to return the total amount for Mr Smith would look like:
Code:
=SUMIFS([COLOR=#0000ff]C2:C7[/COLOR],[COLOR=#ff0000]A2:A7,E2[/COLOR],[COLOR=#008000]B2:B7,">=" &E3[/COLOR],[COLOR=#ff8c00]B2:B7,"<="&E4[/COLOR])
Blue: range you want to sum
Red: name range and name criteria
Green: date range and lower date criteria
Orange: date range and upper date criteria

Note if this is a formula you want to copy down a column, you will want to make some of your ranges absolute references to lock them down, i.e. $C$2:$C$7
(see here: https://www.excel-easy.com/functions/cell-references.html)

Mate, thank you for this! This makes perfect sense and I can see exactly where I went wrong and how I can adapt this for other areas. I really appreciate your help Joe, it is much appreciated!!!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,950
Office Version
  1. 365
Platform
  1. Windows
You are welcome!
:)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,975
Messages
5,599,127
Members
414,291
Latest member
yanqing06

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
Top