Formula to SUM between two dates

[delexcel]

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!

 

[Haseeb Avarakkan]

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.