SUMIF variation?

Excel 2003

I have a sheet with col. A being a list of dates and the other columns being
various figures. I know how to use the sumif function to give me totals for
a particular date in col A. How would I do something similar except giving
me sums for all figures within a particular month?



Thanks in advance... Bob

One way might be to have a few frozen rows at the top of your sheet, then
use the AutoFilter to sort the data, and use the SUBTOTAL functions at the
top of the columns to sum the filtered results.

Vaya con Dios,
Chuck, CABGx3


"Bob Newman" <bobnewman@cox.net> wrote in message
news:T5_kg.112400$Ce1.43449@dukeread01...
> Excel 2003
>
> I have a sheet with col. A being a list of dates and the other columns
being
> various figures. I know how to use the sumif function to give me totals
for
> a particular date in col A. How would I do something similar except
giving
> me sums for all figures within a particular month?
>
>
>
> Thanks in advance... Bob
>
>

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

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Bob Newman" <bobnewman@cox.net> wrote in message
news:T5_kg.112400$Ce1.43449@dukeread01...
> Excel 2003
>
> I have a sheet with col. A being a list of dates and the other columns
being
> various figures. I know how to use the sumif function to give me totals
for
> a particular date in col A. How would I do something similar except
giving
> me sums for all figures within a particular month?
>
>
>
> Thanks in advance... Bob
>
>

Thanks. One question though. What are the 2 dashes before the
(MONTH(A2:A2000)?

Bob

"Bob Phillips" <bob.NGs@xxxx.om> wrote in message
news:O0KjPSlkGHA.4512@TK2MSFTNGP04.phx.gbl...
> =SUMPRODUCT(--(MONTH(A2:A200)=1),B2:B200)
>
> Note that SUMPRODUCT doesn't work with complete columns, you have to
> specify
> a range.
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> "Bob Newman" <bobnewman@cox.net> wrote in message
> news:T5_kg.112400$Ce1.43449@dukeread01...
>> Excel 2003
>>
>> I have a sheet with col. A being a list of dates and the other columns
> being
>> various figures. I know how to use the sumif function to give me totals
> for
>> a particular date in col A. How would I do something similar except
> giving
>> me sums for all figures within a particular month?
>>
>>
>>
>> Thanks in advance... Bob
>>
>>
>
>

"Bob Newman" <bobnewman@cox.net> wrote in message
news:T5_kg.112400$Ce1.43449@dukeread01...
> Excel 2003
>
> I have a sheet with col. A being a list of dates and the other columns
> being various figures. I know how to use the sumif function to give me
> totals for a particular date in col A. How would I do something similar
> except giving me sums for all figures within a particular month?


Dates are in A1:A10
values to summarize are in B1:B10
the month is 2 (FEB):

=SUMPRODUCT((MONTH(A1:A10)=2)*(B1:B10))

Bruno

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Bob Newman" <bobnewman@cox.net> wrote in message
news:vy_kg.112403$Ce1.94109@dukeread01...
> Thanks. One question though. What are the 2 dashes before the
> (MONTH(A2:A2000)?
>
> Bob
>
> "Bob Phillips" <bob.NGs@xxxx.om> wrote in message
> news:O0KjPSlkGHA.4512@TK2MSFTNGP04.phx.gbl...
> > =SUMPRODUCT(--(MONTH(A2:A200)=1),B2:B200)
> >
> > Note that SUMPRODUCT doesn't work with complete columns, you have to
> > specify
> > a range.
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (replace xxxx in the email address with gmail if mailing direct)
> >
> > "Bob Newman" <bobnewman@cox.net> wrote in message
> > news:T5_kg.112400$Ce1.43449@dukeread01...
> >> Excel 2003
> >>
> >> I have a sheet with col. A being a list of dates and the other columns
> > being
> >> various figures. I know how to use the sumif function to give me
totals
> > for
> >> a particular date in col A. How would I do something similar except
> > giving
> >> me sums for all figures within a particular month?
> >>
> >>
> >>
> >> Thanks in advance... Bob
> >>
> >>
> >
> >
>
>

I am having trouble getting it to work, but first of all it looks like from
the description sumproduct multiplies things. Is this correct? I am just
trying to add up all the sales for a particular month.

Bob

"Bruno Campanini" <brunobrowncampanini@gmail.com> wrote in message
news:OHQ4WZlkGHA.1664@TK2MSFTNGP03.phx.gbl...
> "Bob Newman" <bobnewman@cox.net> wrote in message
> news:T5_kg.112400$Ce1.43449@dukeread01...
>> Excel 2003
>>
>> I have a sheet with col. A being a list of dates and the other columns
>> being various figures. I know how to use the sumif function to give me
>> totals for a particular date in col A. How would I do something similar
>> except giving me sums for all figures within a particular month?
>
>
> Dates are in A1:A10
> values to summarize are in B1:B10
> the month is 2 (FEB):
>
> =SUMPRODUCT((MONTH(A1:A10)=2)*(B1:B10))
>
> Bruno
>

The only thing I can think is that the 'date' column has text not dates.

Classically, SUMPRODUCT does multiply arrays, but if you had read that paper
I referenced for you, you would have seen how it's usage has been extended.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Bob Newman" <bobnewman@cox.net> wrote in message
news:KJblg.112425$Ce1.70882@dukeread01...
> I am having trouble getting it to work, but first of all it looks like
from
> the description sumproduct multiplies things. Is this correct? I am just
> trying to add up all the sales for a particular month.
>
> Bob
>
> "Bruno Campanini" <brunobrowncampanini@gmail.com> wrote in message
> news:OHQ4WZlkGHA.1664@TK2MSFTNGP03.phx.gbl...
> > "Bob Newman" <bobnewman@cox.net> wrote in message
> > news:T5_kg.112400$Ce1.43449@dukeread01...
> >> Excel 2003
> >>
> >> I have a sheet with col. A being a list of dates and the other columns
> >> being various figures. I know how to use the sumif function to give me
> >> totals for a particular date in col A. How would I do something
similar
> >> except giving me sums for all figures within a particular month?
> >
> >
> > Dates are in A1:A10
> > values to summarize are in B1:B10
> > the month is 2 (FEB):
> >
> > =SUMPRODUCT((MONTH(A1:A10)=2)*(B1:B10))
> >
> > Bruno
> >
>
>

I'll study further.

Thanks

"Bob Phillips" <bob.NGs@xxxx.om> wrote in message
news:eOpjgKvkGHA.3936@TK2MSFTNGP05.phx.gbl...
> The only thing I can think is that the 'date' column has text not dates.
>
> Classically, SUMPRODUCT does multiply arrays, but if you had read that
> paper
> I referenced for you, you would have seen how it's usage has been
> extended.
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> "Bob Newman" <bobnewman@cox.net> wrote in message
> news:KJblg.112425$Ce1.70882@dukeread01...
>> I am having trouble getting it to work, but first of all it looks like
> from
>> the description sumproduct multiplies things. Is this correct? I am
>> just
>> trying to add up all the sales for a particular month.
>>
>> Bob
>>
>> "Bruno Campanini" <brunobrowncampanini@gmail.com> wrote in message
>> news:OHQ4WZlkGHA.1664@TK2MSFTNGP03.phx.gbl...
>> > "Bob Newman" <bobnewman@cox.net> wrote in message
>> > news:T5_kg.112400$Ce1.43449@dukeread01...
>> >> Excel 2003
>> >>
>> >> I have a sheet with col. A being a list of dates and the other columns
>> >> being various figures. I know how to use the sumif function to give
>> >> me
>> >> totals for a particular date in col A. How would I do something
> similar
>> >> except giving me sums for all figures within a particular month?
>> >
>> >
>> > Dates are in A1:A10
>> > values to summarize are in B1:B10
>> > the month is 2 (FEB):
>> >
>> > =SUMPRODUCT((MONTH(A1:A10)=2)*(B1:B10))
>> >
>> > Bruno
>> >
>>
>>
>
>