Formula to SUM between two dates

delexcel

Board Regular
Hi,

I am looking to create a formula that SUMS all the values between two specific criteria:

Criteria 1 - whether a person has been paid already (in the dataset, there is a column that is titled 'PAID' and has YES/NO values)

Criteria 2 - is between two 'weeks'.

An example, I may want to calculate the total owing to people between weeks 1-4 that have not been paid as yet.

In the dataset there is columns for a 'persons name', one for 'week', one for 'paid', and one for a '\$ value', e.g.

Name | Week | Paid? | \$ |

Person x | 1 | No | 200
Person y | 1 | No | 100
Person z | 2 | No | 50
Person q | 1 | Yes | 10
Person w | 2 | Yes | 5

I have two input cells to enter the period between which weeks you are looking to sum

A1 = Weeks, A2 = 1, A3 = 2

So the formula I am looking to create assesses cells A2 & A3 AND assesses the 'Paid' column.

For instance, say if A2 & A3 were 1 & 1 (i.e. between weeks 1 and 1, e.g. just for one week), and were just looking for unpaid people.

Then the formula result would be 300 (being the Person x & Person y, 200 + 100 ... with Person q excluded as they have been paid, and Persons z & w are from week 2).

A second example ....

Say A2 = 1 and A3 = 2 ...

Then the formula result would be 350 (being the Person x & Person y & Person z, 200 + 100 + 50 ... with Persons q & w excluded as they have been paid).

And final example ...

Say A2 = 2 and A3 = 2 ...

Then the formula result would be 50 (being just Person z, 50 ... with Person w excluded as they have been paid, and all other Persons are from week 1).

Any help would be greatly appreciated!

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Haseeb Avarakkan

Well-known Member
Hello, Try

If you are using excel 2007 or later;

=SUMIFS(Sheet1!D:D,Sheet1!B:B,">="&A2,Sheet1!B:B,"<="&A3,Sheet1!C:C,"No")

If you are Excel 2003 or earlier;

=SUMPRODUCT(Sheet1!\$D\$2:\$D\$1000,--(Sheet1!\$B\$2:\$B\$1000>=A2),--(Sheet1!\$B\$2:\$B\$1000<=A3),--(Sheet1!\$C\$2:\$C\$1000="No"))

A:A = Name
B:B = Week
C:C = Paid?
D:D = \$

Also, check out the DSUM function in the help file which is faster.

Replies
3
Views
72
Replies
3
Views
101
Replies
1
Views
47
Replies
2
Views
27
Replies
5
Views
112

1,109,420
Messages
5,528,668
Members
409,829
Latest member
CFreeamaz

This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...