Counting in date range with 2 criteria

[Helix242]

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!

 

[Joe4]

This link shows you how to do that:
https://exceljet.net/formula/sum-if-date-is-between

 

[Helix242]

This link shows you how to do that:
https://exceljet.net/formula/sum-if-date-is-between

Thank you, but this doesn't really help. I know how to get the totals between 2 dates. I want to add an additional criteria point. So I can calculate the amounts between the 2 dates for each person and not just the whole amounts between the 2 dates. Is this possible?

Many thanks

 

[Joe4]

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]

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;

=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]

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]

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]

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:

=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)

 

[Helix242]

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:

=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]

You are welcome!