Brain Dead Excel help

[keepngoal]

I am hoping to get some help in an area I am typically proficient in.

ColumnA | ColumnB
Date | Number

I need to look up date ranges (monthly and then quarterly) in ColumnA and sum the appropriate cell in ColumnB.

I am stuck in a sumif mode....

=sumif(A1:A24,">01/01/2009",B1:B24) gets me all that are greater than January 1st of this year. However I would like to have the formula look at the criteria for the first quarter from a list of dates in column A: >01/01/2009 AND <04/01/2009

this does NOT work or any variations thereof:
=sumif(A1:A24,">01/01/2009"AND "<04/01/2009",B1:B24)

Do I need an array formula?

Any help is appreciated.

-keep

 

[jdoggivjc]

In the words of Peanut (Jeff Dunham's puppet), "VRROOOOOOOOOOM!!!" (as hand goes over head)

 

[tamjam]

Have an equation that is the total sum - sum before 01/01/09 - sum after 04/01/09. BAM

So the final equation would be....

sum(B1:B24) - sumif(A1:A24,"<01/01/09",B1:B24) - sumif(A1:A24,">04/01/09",B1:B24)

 

[mred]

=SUMIFS(B1:B24,A1:A24,">01/01/2009",A1:A24,"<04/01/2009")

EDIT: This is only Excel 2007 or newer. If you have older, I can try to figure out another way.

 

[keepngoal]

In one cell you could sumif the date is before 01/01/09 and in another cell sumif the date is after 04/09/09. Then in a third cell the have an question that is the total sum - sum before 01/01/09 - sum after 04/09/09. BAM

I guess I could do...

+sum(all dates)-sumif(>date)-sumif(<date)

kinda seems messy to me .... but it does work. Sometimes I look for the perfect tool (formula) for the task, taking longer to search than what the tasks requires with adequate tools. :no:

-keep

 

[tamjam]

Messy, sure, but it works. You now owe me a favor. I'm not sure when or how, but I will be calling on you for that favor sometime in the future.

 

[keepngoal]

=SUMIFS(B1:B24,A1:A24,">01/01/2009",A1:A24,"<04/01/2009")

EDIT: This is only Excel 2007 or newer. If you have older, I can try to figure out another way.

Thanks... however 2003. :sad:

 

[chadm]

Thanks... however 2003. :sad:

2003 isn't bad for you. I figured you would be using Lotus123:cute:

 

[keepngoal]

SuperCalc!!!!

 

[mred]

Got it.

=SUMPRODUCT((B1:B24),1*(A1:A24>VALUE("1/1/2009")),1*(A1:A24<VALUE("4/1/2009")))

The "1*" in the equation converts the arrays of booleans into arrays of 0,1 numbers.

 

[TxCycloneFan]

Messy, sure, but it works. You now owe me a favor. I'm not sure when or how, but I will be calling on you for that favor sometime in the future.

Yes Godfather.

 

[keepngoal]

Got it.

=SUMPRODUCT((B1:B24),1*(A1:A24>VALUE("1/1/2009")),1*(A1:A24<VALUE("4/1/2009")))

The "1*" in the equation converts the arrays of booleans into arrays of 0,1 numbers.

Sweet... that is it!!! Thanks mred!! Thank you!

-keep