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

SUMIF variation?
 

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" 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

SUMIF variation?
 

=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" 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

SUMIF variation?
 

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

Bob

"Bob Phillips" wrote in message
...
=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" 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

SUMIF variation?
 

"Bob Newman" 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

SUMIF variation?
 

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" 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" wrote in message
...
=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" 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

SUMIF variation?
 

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" wrote in message
...
"Bob Newman" 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

SUMIF variation?
 

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" 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" wrote in message
...
"Bob Newman" 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

SUMIF variation?
 

I'll study further.

Thanks

"Bob Phillips" wrote in message
...
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" 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" wrote in message
...
"Bob Newman" 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