Thread: Need cell formula to subtotal gross by month for a quarter

Need Gross subtotaled by 1st month, 2nd month, & 3rd month of a quarter end

In Cell I2:
Quarter End 9/30/05 (other possible quarter ends 3/31/05, 6/30/05, & 12/31/05)

Data exits in cells A3 thru D37. The following is an example of the quarter ended
9-30-05.

__ _A__ ____B____ __C___ ___D___
_3 Ck # Ck Date Emp # Gross
_4 4151 07/01/05 145 170.63
_5 4152 07/01/05 155 200.00
_6 4156 07/15/05 145 210.00
_7 4172 07/22/05 145 277.50
_8 4174 07/22/05 145 232.50
_9 4208 08/05/05 145 127.50
10 4217 08/12/05 145 279.38
11 4218 08/12/05 156 192.00
12 4235 09/15/05 145 288.75
13 4236 09/15/05 156 480.00
14 4262 09/16/05 145 16.88
15 4263 09/16/05 157 66.00

Need formula for E6, E7, & E8 to get the following results:
In E6 1st Month Gross = 170.63
In E7 2nd Month Gross = 598.88
In E8 3rd Month Gross = 851.63

Any Ideas? mikeburg

Sorry, I meant to use 1,090.63 as the desired result; not 170.63.

The desired result is merely adding the gross for each month.

Thanks so much. mikeburg

Try the following...

E6:

=SUMPRODUCT(--($B$4:$B$15-DAY($B$4:$B$15)+1=DATE(YEAR($I$2),MONTH($I$2)-2
,1)),$D$4:$D$15)

For E7, change '-2' to '-1'

For E8, remove the '-2' bit

Hope this helps!

In article <mikeburg.1xu3q4_1130875786.207@excelforum-nospam.com>,
mikeburg <mikeburg.1xu3q4_1130875786.207@excelforum-nospam.com> wrote:

> Need Gross subtotaled by 1st month, 2nd month, & 3rd month of a quarter
> end
>
> In Cell I2:
> Quarter End 9/30/05 (other possible quarter ends 3/31/05, 6/30/05, &
> 12/31/05)
>
> Data exits in cells A3 thru D37. The following is an example of the
> quarter ended
> 9-30-05.
>
> __ _A__ ____B____ __C___ ___D___
> _3 Ck # Ck Date Emp # Gross
> _4 4151 07/01/05 145 170.63
> _5 4152 07/01/05 155 200.00
> _6 4156 07/15/05 145 210.00
> _7 4172 07/22/05 145 277.50
> _8 4174 07/22/05 145 232.50
> _9 4208 08/05/05 145 127.50
> 10 4217 08/12/05 145 279.38
> 11 4218 08/12/05 156 192.00
> 12 4235 09/15/05 145 288.75
> 13 4236 09/15/05 156 480.00
> 14 4262 09/16/05 145 16.88
> 15 4263 09/16/05 157 66.00
>
> Need formula for E6, E7, & E8 to get the following results:
> In E6 1st Month Gross = 170.63
> In E7 2nd Month Gross = 598.88
> In E8 3rd Month Gross = 851.63
>
> Any Ideas? mikeburg

Works great! Thanks so very much for your help.
mikeburg