Sum exact range based on criteria

[exceldiehardfan]

Hi, I would appreciate if someone could help me out with this Excel problem. I would like to sum a quarter's total based on the user's date input.

Here's what the data would look like:

Month Total
Jan 10
Feb 20
Mar 30
Apr 40
May 50
Jun 60
Jul 70
Aug 80
Sep 90
Oct 100
Nov 110
Dec 120

For example, May total should be 90, ie Apr + May
Sep should be 240, ie Jul + Aug + Sep
Oct should be 100, ie Oct totals only

I would appreciate any input. Thanks so much!

[sweep]

Presuming your table starts at cell A1

in C2, and copied down

=SUM(OFFSET(B2,0,0,CHOOSE(1+MOD(MATCH(A2,$A$2:$A$13,0),3),-3,-1,-2),1))

[TonyB51]

Hi,

Based your request, I believe the attached sheet provides a simple summary, where the monthly totals have already been compiled.

Should you wish to calculate the number of transactions for the month, a different method would be used to achieve the same summary result.

Cheers

Tomy

[exceldiehardfan]

Presuming your table starts at cell A1

in C2, and copied down

=SUM(OFFSET(B2,0,0,CHOOSE(1+MOD(MATCH(A2,$A$2:$A$13,0),3),-3,-1,-2),1))

Worked like a charm! Thank you very much!

[exceldiehardfan]

Hi,

Based your request, I believe the attached sheet provides a simple summary, where the monthly totals have already been compiled.

Should you wish to calculate the number of transactions for the month, a different method would be used to achieve the same summary result.

Cheers

Tomy

Thank you Tomy! So far, no need to do the transactions for the month as I only get the totals and work from those data. Whew!