need help with formula
 

I need some help with a formula:

I have a list of weights and the dates they were received. i need to find
the total tonnage for each month....and the average for each month. i.e. sum
all the weights that were received in jan, then feb, etc.....

I'm not sure where to start, but something like this?...

=sum B1:B10 if A1:A10 is "jan" or "01"
=sum B1:B10 if A1:A10 is "feb" or "02"
....etc..

Doesn that make any sense?

Jeremy


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200508/1

Jeremy

try this, assuming your tonnages are in column B:
=SUMPRODUCT((A1:A10="jan")*(B1:B10))


"jeremy via OfficeKB.com" wrote:

I need some help with a formula:

I have a list of weights and the dates they were received. i need to find
the total tonnage for each month....and the average for each month. i.e. sum
all the weights that were received in jan, then feb, etc.....

I'm not sure where to start, but something like this?...

=sum B1:B10 if A1:A10 is "jan" or "01"
=sum B1:B10 if A1:A10 is "feb" or "02"
....etc..

Doesn that make any sense?

Jeremy


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200508/1

Oh, and for the average - this is a really chintzy way to do it, and I'm sure
there are more sophisticated ways, but it's how I do it - go to your first
blank column, put 1 in the first cell and drag it down to infinity; then:
=SUMPRODUCT((A1:A10="jan")*(B1:B10))/SUMPRODUCT((A1:A10="jan")*(C1:C10))

"MikeDH" wrote:

Jeremy

try this, assuming your tonnages are in column B:
=SUMPRODUCT((A1:A10="jan")*(B1:B10))


"jeremy via OfficeKB.com" wrote:

I need some help with a formula:

I have a list of weights and the dates they were received. i need to find
the total tonnage for each month....and the average for each month. i.e. sum
all the weights that were received in jan, then feb, etc.....

I'm not sure where to start, but something like this?...

=sum B1:B10 if A1:A10 is "jan" or "01"
=sum B1:B10 if A1:A10 is "feb" or "02"
....etc..

Doesn that make any sense?

Jeremy


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200508/1

Mike,
Thanks, I tried this formula =SUMPRODUCT((U9:U272="jan")*(T9:T272))

but the result was zero--should have been a value. Any thoughts? the date
is entered as mm/dd/yyyy in the column U.

jeremy


MikeDH wrote:
Jeremy

try this, assuming your tonnages are in column B:
=SUMPRODUCT((A1:A10="jan")*(B1:B10))

I need some help with a formula:

[quoted text clipped - 11 lines]

Jeremy


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200508/1

Here we go - I didn't think about date format... This assumes your cells are
formatted in date:
=SUMPRODUCT((MONTH(U9:U272)=1)*(T9:T272))
Excel stores dates as serial numbers (Julian date count since January 1,
1900), so you use the Month function to tell you the month from that serial
number.

"jeremy via OfficeKB.com" wrote:

Mike,
Thanks, I tried this formula =SUMPRODUCT((U9:U272="jan")*(T9:T272))

but the result was zero--should have been a value. Any thoughts? the date
is entered as mm/dd/yyyy in the column U.

jeremy


MikeDH wrote:
Jeremy

try this, assuming your tonnages are in column B:
=SUMPRODUCT((A1:A10="jan")*(B1:B10))

I need some help with a formula:

[quoted text clipped - 11 lines]

Jeremy


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200508/1

Thanks Mike--worked out great. I might have a couple more questions too....

Jeremy



MikeDH wrote:
Here we go - I didn't think about date format... This assumes your cells are
formatted in date:
=SUMPRODUCT((MONTH(U9:U272)=1)*(T9:T272))
Excel stores dates as serial numbers (Julian date count since January 1,
1900), so you use the Month function to tell you the month from that serial
number.

Mike,
Thanks, I tried this formula =SUMPRODUCT((U9:U272="jan")*(T9:T272))
[quoted text clipped - 14 lines]

Jeremy


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200508/1

In X2 enter:

1-Jan-05

which is the first day date of the month/year of interest, that is, Jan
2005.

In Y2 enter:

=EOMONTH(X2,0)

Then invoke a SumIf formula:

=SUMIF($U$9:$U$272,"="&X2,$T$9:$T$272)-SUMIF($U$9:$U$272,""&Y2,$T$9:$T$272)

jeremy via OfficeKB.com wrote:
Mike,
Thanks, I tried this formula =SUMPRODUCT((U9:U272="jan")*(T9:T272))

but the result was zero--should have been a value. Any thoughts? the date
is entered as mm/dd/yyyy in the column U.

jeremy


MikeDH wrote:

Jeremy

try this, assuming your tonnages are in column B:
=SUMPRODUCT((A1:A10="jan")*(B1:B10))


I need some help with a formula:


[quoted text clipped - 11 lines]

Jeremy




--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.