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

[da_vide78]

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!

 

[Andrew Poulsom]

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]

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]

still can't get it to work.

 

[schielrn]

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]

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.
How do i add that?

 

[Andrew Poulsom]

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]

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]

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]

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!