# **Wanted** - Formula for totals using date ranges

#### da_vide78

##### Board Regular
Morning all!

I need to find a formula which will give me a total of £'s between a start and end date.

MY data is an extract with names, individual dates (Ie, 01/05/08, 02/05/08, etc) and costs per day. What i want to do is show a total for the month using the start and end date of that month to add up all the costs within.

Can anyone help cos my brain just won't work on this!

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### Andrew Poulsom

##### MrExcel MVP
Assuming your dates in column A, your values are in column B, C1 contains the start date and D1 contains the end date:

=SUMPRODUCT(--(A1:A100>=C1),--(A1:A100<=D1),(B1:B100))

Adjust the range references to suit but avoid entire columns.

#### da_vide78

##### Board Regular
Do the columns have to be next to each other to work?

I have my dates in Column G, and costs in Column K and doesn't work....

#### da_vide78

##### Board Regular
still can't get it to work.

#### schielrn

##### Well-known Member
Did you adjust his formula to this:

=SUMPRODUCT(--(G1:G100>=C1),--(G1:G100<=D1),(K1:K100))

Cells C1 is still start date and D1 is still end date. Is this similiar to what you have?

#### da_vide78

##### Board Regular
I sorted that out now - cheers. Another step i would like to add...

Now i can see the costs between 2 dates, i want to add another option. I want to see the costs between the 2 dates BUT split the costs into 2 activites.
I have spend for activity A and activity B.

#### Andrew Poulsom

##### MrExcel MVP
If your costs are in column H and the one you want is in E1:

=SUMPRODUCT(--(G1:G100>=C1),--(G1:G100<=D1),--(H1:H100=E1),(K1:K100))

#### da_vide78

##### Board Regular
Thanks Andrew but that's not worked..
So the columns i'm using are as follows;

Activity - Col F
Dates - Col G
Cost - Col K
"summary"Tab - Start and end date references

See what i'm using so far is;

=SUMPRODUCT(--(G2:G2500>=Summary!K4),--(G2:G2500<=Summary!L4),(K2:K2500))

I was thinking that adding --(F2:F2500="Opex") somwhere would work, but it hasn't..

Am i thinking about it right?

#### schielrn

##### Well-known Member
What doesn't work about it? Does it return an error or 0? Is eveything formatted the same between the 2 sheets, like column G is formatted the same as Summary K4 and L4? Column F has just Opex in it and no extra blanks or anything?

=SUMPRODUCT(--(G2:G2500>=Summary!K4),--(G2:G2500<=Summary!L4),--(F2:F2500="Opex"),(K2:K2500))

Hope that helps.

#### da_vide78

##### Board Regular
Sorted it.

for some reason, the data i'm referencing has spelt Opex, with a Zero "0pex"..

stupid reports!

All working now - thanks guys for you persistance help!

Replies
2
Views
44
Replies
1
Views
98
Replies
1
Views
49
Replies
1
Views
292
Replies
3
Views
112

1,191,614
Messages
5,987,690
Members
440,104
Latest member
thigarette

### 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.

### Which adblocker are you using?

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

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