SUMIF variation?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob Newman - 17 Jun 2006 22:00 GMT

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

Reply to this Message

CLR - 17 Jun 2006 22:15 GMT

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

> Excel 2003
>
[quoted text clipped - 4 lines]
>
> Thanks in advance... Bob

Reply to this Message

Bob Phillips - 17 Jun 2006 22:25 GMT

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

> Excel 2003
>
[quoted text clipped - 4 lines]
>
> Thanks in advance... Bob

Reply to this Message

Bob Newman - 17 Jun 2006 22:30 GMT

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

Bob

> =SUMPRODUCT(--(MONTH(A2:A200)=1),B2:B200)
>
[quoted text clipped - 21 lines]
>>
>> Thanks in advance... Bob

Reply to this Message

Bob Phillips - 17 Jun 2006 22:50 GMT

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)

> Thanks. One question though. What are the 2 dashes before the
> (MONTH(A2:A2000)?
[quoted text clipped - 26 lines]
> >>
> >> Thanks in advance... Bob

Reply to this Message

Bruno Campanini - 17 Jun 2006 22:38 GMT

> 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

Reply to this Message

Bob Newman - 18 Jun 2006 13:30 GMT

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

>> Excel 2003
>>
[quoted text clipped - 10 lines]
>
> Bruno

Reply to this Message

Bob Phillips - 18 Jun 2006 17:16 GMT

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)

> 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
[quoted text clipped - 16 lines]
> >
> > Bruno

Reply to this Message

Bob Newman - 18 Jun 2006 21:13 GMT

I'll study further.

Thanks

> The only thing I can think is that the 'date' column has text not dates.
>
[quoted text clipped - 35 lines]
>> >
>> > Bruno

Reply to this Message