Excel formula challenge

[dwest100]

Hi,
I have a range consisting of two columns, "start date" and "total".
"Start Date" format is date.
"Total" format is dollars.

The number of rows in the range will vary.

In a cell to the right of the range I need a formula that searches the "Start Date" column for all dates in April and sums all the corresponding values found in the "Total" column. (The resulting value in this cell would be the grand total of all "Totals" in April.)

I don't want to use the Subtotals capability built into Excel. I just need the specific formula described.

Many thanks!

 

[GlennUK]

Just use 2 SUMIF functions, one subtracted from the other:

=SUMIF(DateColRange,"<1/5/2006",TotColRange)-SUMIF(DateColRange,"<1/4/2006",TotColRange)

( dates are in UK format, so adjust if you are US or other country that uses mm/dd/yyyy )

If you want automatically adjusting ranges, define the names DateColRange and TotColRange as dynamically defined names, using a formula similar to this ( Insert/Name/Define ):

=OFFSET($A$1,1,0,COUNTA($A:$A)-1,1)

otherwise replace those names in the formula with the required actual ranges.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[AnotherHiggins]

Another way to go:
=SUMPRODUCT((A2:A65536>=VALUE("4/1/2006"))*(A2:A65536<=VALUE("4/30/2006"))*(B2:B65536))

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[GlennUK]

Alternatively use an array formula:

=SUM(IF(A2:A65536>=VALUE("1/April/2006"),IF(A2:A65536<=VALUE("30/April/2006"),B2:B65536,0),0))

entered using Ctrl-Shift0-Enter instead of Enter.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[firefytr]

For named ranges, check out Bob Phillips page here:

http://www.xldynamic.com/source/xld.LastValue.html#last_any

If the data range is non-contiguous I would highly recommend against an OFFSET/COUNTA type of solution.

-----------
Regards,
Zack Barresse

 

[dwest100]

Thanks to all. problem solved!