Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a better way to do this?
This is what I have
E16:E39 are various dates (some duplicated) G16:G39 are numbers When H16:H39 = "in" or "ti" then the values in G16:G39 should be added, however When H16:H39 = "dd" or "ot" or "s" or "m" the values in G16:G39 should be subtracted. I've come up with: =SUMPRODUCT(--(E16:E39=DATE(2008,1,1)),--(H16:H39="IN")--(H16:H39="TI")-(H16:H39="DD")-(H16:H39="OT")-(H16:H39="S")-(H16:H39="M"),G16:G39) This works but can it be consolidated? Also, I need to enter this for every day of the year and when I drag it the date stays the same and only the reference cells change. I need to know the sum of the items for each day of the year so I'm wondering what is the fastest way. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a better way to do this?
It's not any shorter, but if you had more criteria to add to your formula,
you could try: =SUMPRODUCT(--($E$16:$E$39=DATE(2008,1,1)),($H$16:$H$39="IN")+($ H$16:$H$39="TI")-ISNUMBER(MATCH($H$16:$H$39,{"DD","OT","S","M"},0)) ,$G$16:$G$39) the $ fixes the row/column references so that they won't change as you copy the formula down or across (but I'm assuming you don't want those to change??) I would put the dates in a separate cell. If you put 1/1/08 in A1 and 1/2/08 in A2, then select A1:A366 and click Edit/Fill/Series you can have every day of the year in column A. Then if you enter your formula in cell B1 and replace Date(2008,1,1) in you formula with A1 and copy your formula down column B I believe you should be able to get the results you want. If putting the dates in a separate cell is not an option, then use something similar like Date(2008, 1, Rows(A$1:A1)) and copy the formula down. If you are copying the formula across, then Date(2008, 1, Columns($A1:A1)) "Christy" wrote: This is what I have E16:E39 are various dates (some duplicated) G16:G39 are numbers When H16:H39 = "in" or "ti" then the values in G16:G39 should be added, however When H16:H39 = "dd" or "ot" or "s" or "m" the values in G16:G39 should be subtracted. I've come up with: =SUMPRODUCT(--(E16:E39=DATE(2008,1,1)),--(H16:H39="IN")--(H16:H39="TI")-(H16:H39="DD")-(H16:H39="OT")-(H16:H39="S")-(H16:H39="M"),G16:G39) This works but can it be consolidated? Also, I need to enter this for every day of the year and when I drag it the date stays the same and only the reference cells change. I need to know the sum of the items for each day of the year so I'm wondering what is the fastest way. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a better way to do this?
Christy,
Use a column of helper formulas: starting in row 16 of a blank column =G16* IF(OR(H16="in",H16="ti"),1,-1) and then copy down Then use a pivot table (select all the data, and use Data / Pivot Table....) with dates as the row field, and the new column as the data field, set to SUM. HTH, Bernie MS Excel MVP "Christy" wrote in message ... This is what I have E16:E39 are various dates (some duplicated) G16:G39 are numbers When H16:H39 = "in" or "ti" then the values in G16:G39 should be added, however When H16:H39 = "dd" or "ot" or "s" or "m" the values in G16:G39 should be subtracted. I've come up with: =SUMPRODUCT(--(E16:E39=DATE(2008,1,1)),--(H16:H39="IN")--(H16:H39="TI")-(H16:H39="DD")-(H16:H39="OT")-(H16:H39="S")-(H16:H39="M"),G16:G39) This works but can it be consolidated? Also, I need to enter this for every day of the year and when I drag it the date stays the same and only the reference cells change. I need to know the sum of the items for each day of the year so I'm wondering what is the fastest way. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|