SUMIF variation?

[Bob Newman]

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

 

[CLR]

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 Phillips]

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

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

Bob

 

[Bruno Campanini]

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

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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]

I'll study further.

Thanks

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)