Return value in X if date in Y falls between two dates - Help!

**nephilim3uk** · 07-08-2012, 09:39 AM

Hi all

I am struggling with what feels like it should be a simple solution, and it's frustrating!
I am collecting data by date and amount, and I want to add the values into the right column on a given table so that I can use it to calculate total amount in a particular month.
I have attached a simple example sheet to better explain what I mean.
The formula I am using is: =IF($B$2>=DATE(2012,1,1)<=DATE(2012,1,31),$C$2,0)

Any help will be greatly appreciated.

many thanks

Richard

**MarvinP** · 07-08-2012, 09:47 AM

Hi Richard,

Have you looked at using a AND() or a MONTH() in your formula?
Easier is a Pivot Table - I'm assuming you have a lot more dates and amounts to deal with. See attached for a simple Pivot. Also Pivots are easier with newer versions of Excel.

see http://chandoo.org/wp/2009/11/17/gro...-pivot-tables/

**nephilim3uk** · 07-08-2012, 09:53 AM

I will take a look at that, thank you.
I need it to work in Excel2003 as that is what the majority of users currently have!

thanks again.

**icestationzbra** · 07-08-2012, 09:56 AM

two different formulae to achieve what you are looking for:

Please Login or Register  to view this content.

if i understand your requirement correctly, this could also work:

Please Login or Register  to view this content.

**nephilim3uk** · 07-08-2012, 10:13 AM

#icestationzbra
Fantastic, second option works a treat, easier to manage aswell. Many thanks.

I will still look at pivot tables though as I haven't used before.

Thanks All

Richard