need help with formula

[jeremy via OfficeKB.com]

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

 

[Guest]

Jeremy

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

 

[Guest]

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

 

[jeremy via OfficeKB.com]

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

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

 

[Guest]

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

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

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

 

[jeremy via OfficeKB.com]

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

Jeremy

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]

 

[Aladin Akyurek]

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)

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

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.