Is there a better way to do this?

[Christy]

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.

 

[JMB]

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

 

[Bernie Deitrick]

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