Sumif a column within named range using the difference of two other columns in that same named range as the criteria

Question

I have 3 columns: A:date 1, B:date 2, C: # of units

To keep that table dynamic (i.e. can add or remove rows to the table), I named that range "data"

I want to sum data in column C if B-A is less than 4 yrs.

The problem is that the data table is in worksheet 1 and the formula is in worksheet 2. (so that other users can add rows to the table without concerning themselves with formulas).

Please help suggest how to accomplish the sum of column C. I thought about creating a dummy column in worksheet 2 using indirect function, but how would that be affected by additions to the data table? that might not be the simplest solution. I also thought about using a formula for the criteria of a sumif function, but I don't know how to reference a range within the named range... I hope that was clear :)

Thanks in advance.

Answer 1

Update: I solved it: {=SUM(IF(DATEDIF(Date1,TODAY(),"y")<5,NumUnits))}

The syntax of the SumIF prevented the manipulation of data within the range.  So, I thought, what if I just used a if and sum the results. BTW this must be entered as an array.

Hello Yu B,

I tested this and could not get it to work. I wonder if you made an error in positing the formula.

I have a column of dates between 3 and 6 years prior to today and named the column "Date1". another column of numerics and named "NumUnits". I entered the formula as an Array formula. I get  #VALUE!

I tested the column of dates with just DateDif to ensure that the dates were in the 3 to 6 years prior to today.

Answer 2

Hi OssieMac and Jeeped,

I originally had the dateif function in column C and the unit # in column D. However, inserting rows does not copy over the formula. That's not a big deal if I was owning this, but need to dummy proof it for others.

In addition, I do have other data in the worksheet.

I'll try named ranges for each column next. But, I still wonder if there is a means to work with a data range within the named range like the Index function. I was not able to implement the Index function successfully.

update: I named each range but got stump with the syntax  when trying to write the sumif formula.

Named ranges: NumUnits, Date1, Date2 (to simplify I used Today() for Date 2)

=SUMIF(NumUnits,DATEDIF(Date1,TODAY(),"y")<4)

The formula above did not work. I also tried ctrl+shift to make it an array formula. I think the issue is syntax.

I think the solution is to pre-define the size of the table re-organize worksheet 1 such that the dynamic table is situated below the rest of the data. For example, the table starts in row 45 to row 60; and rows 61 on are blank. Then, in Worksheet 2 use the Indirect function to copy the data from row 45 to a row 2000 (assuming that the size of the table after new additions and removals will not exceed a certain limit).

Doing so will enable me to apply formulas to the mirrored data without worrying about the affect of adding and removing rows to the original data table. What do you guys think? ... not the most elegant but is simple and band-aids the problem. :)

Update: I solved it: {=SUM(IF(DATEDIF(Date1,TODAY(),"y")<5,NumUnits))}

The syntax of the SumIF prevented the manipulation of data within the range.  So, I thought, what if I just used a if and sum the results. BTW this must be entered as an array.

Answer 3

I would create an aditional column and use Yearfrac function  to calculate the number of years between the 2 dates as follows: (Lookup Yearfrac in Help for more info on the last parameter.

=YEARFRAC(B2,A2,1)

Then I would use the SumIf function (and as Yu B suggests, address the entire columns in the formula) as follows:

=SUMIF(Sheet1!D:D,"<4",Sheet1!C:C)

The above formula  assumes that the Yearfrac formula is in column D of Sheet1 but you can put it on another sheet if you want to.

By addressing the entire columns then it does not matter how many rows are added or deleted. If you keep the Yearfrac on the same sheet and adjacent to the other data then as dates and numbers are added to the previous columns then the formula in column D should automatically copy itself to the next line. (But check this because it depends on how the extra data is added.)

Answer 4

Is there data below your values in columns A, B & C on Sheet1? Are there numerous formulas which reference your named range in Sheet1? If neither case is answered Yes, then you could simply use the entire columns within formula like this,

=SUMPRODUCT((Sheet1!B:B-Sheet1!A:A<1461)*(Sheet1!C:C))

Alternately, you could create three named ranges on Sheet1. I'd suggest MyRangeA, MyRangeB and MyRangeC.