how do i sum data by months? (data is in columns by month. In jul.

[edtec]

I have data in columns by month. Each month, I would like to change the month
on my worksheet and have excel automatically sum all of the columns up to
that month. I created an "IF" function (if d4=July, sum(....)), however,
Excel will only let me nest 7 functions. I need 12. Any ideas?

 

[Don Guillett]

try this where b1 has the month number
=sumif(month(a2:a200),b1,b2:b200)

 

[Peo Sjoblom]

If you have dates use

=SUMPRODUCT(--(MONTH(A2:A200)=7),B2:B200)

for July
if you have text like "July"

=SUMIF(A2:A200,"July",B2:B200)

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Frank Kabel]

Hi Don
SUMIF won't accept a function call for the first parameter. So your
formula returns an error for me ))

 

[Don Guillett]

I should have remembered that but I usually use sumproduct for all of it
anyway so I should have suggested that.

 

[Myrna Larson]

AIR, both SUMIF and COUNTIF are derived internally from the "D" functions, and
they require a range for the first argument. A literal array, or an array
created on-the-fly by an array formula, will not work.

 

[Don Guillett]

=sumproduct((month(a2:a200=2)*1) will count dates with Feb.

=sumproduct((month(a2:a200=2)*b2:b200) will sum b for month 2 in a