SUMIF Formula for Months !!!

[AznDragon533]

Hello,

Please help, I need to do a quick SUmIF formula

IF the Range B5:B9 falls under June, then add that particular Line item on Column C


so the formula for C12 should be $200

please see attachment.

thanks,

[BenMiller]

Can't open your attachment and I don't know if you need SUMIF, but what about
=SUMPRODUCT(--(MONTH(B5:B9)=6),--(C5:C9))

[NBVC]

Try:

=SUMIF($B$5:$B$9,">="&EOMONTH(B12,-1)+1,$C$5:$C$9)-SUMIF($B$5:$B$9,">"&EOMONTH(B12,0),$C$5:$C$9)

if you get #NAME error, go to Tools|Addins and check Analysis Toolpak Addin

[Ace_XL]

Use..in C12

=SUMPRODUCT(--(MONTH($B$5:$B$9)=MONTH(B12)),--($C$5:$C$9)) and drag down

[NBVC]

note:... for larger lists, SUMIF is more efficient than Sumproduct....

[oeldere]

or an pivot table.

[ben_hensel]

Let's take advantage of two things:

1) Excel counts dates forward from a reference (1 = Jan 1, 1900 in the 1900 date system).

2) You're indexing with dates in the B12:B18 array for the output table.

So:

c12 =SUMIFS($C$5:$C$9,$B$5:$B$9,">="&B12, $B$5:$B$9,"<"&B13)

Pull down to c17.

Because b19 = 0 (when assessed by the SUMIFS), you'll get 0 for December no matter what, though, so that column alone requires a different formula; just chop off the last two arguments so that

c18 =SUMIFS(C11:C15,B11:B15,">="&B18)

[Ace_XL]

Note - The SUMIFS function will not work in 2003 version

[NBVC]

Note also, my original formula was given assuming you are using Excel 2003, as per your user info, but your file is .xlsx.. so you can use simplified:

=SUMIFS($C$5:$C$9,$B$5:$B$9,">="&EOMONTH(B12,-1)+1,$B$5:$B$9,"<="&EOMONTH(B12,0))

[AznDragon533]

thanks so much!! rep.