Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
put formula results into a different cell if it is empty | Excel Worksheet Functions | |||
how do i write a formula and keep in in formula form, so it DOESN. | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |