0

can't wrap my mind around this one. We have data in a sheet with columns with a date header (Jun 2017,Jul 2017,Aug 2017) and these are 'rolling headers' (they give information from the current date - Now in March the first month shown is April 2017 - In April the first header will be May 2017. I need to be able to sum the cost in those column based on a certain period, like giving the total for a full year two years from a given date. I tried the below, but it gives me a #Value error

=SUMIFS(Calc1!EZ2:HR10000,Calc1!EZ1:HR1,">"&DATE((YEAR(Calc1!EX2)+2),1,1),Calc1!EZ1:HR1,"<"&DATE((YEAR(Calc1!EX2)+3),1,1))

Also marking it as an array formula gives me the same result.

What am I doing wrong and is there a better way (use aggregate?).

Thanks, Rene

Improve this question
7
  • Are your date headers true dates or are they test that look like dates?
    – Scott Craner
    Commented Mar 26, 2017 at 18:16
  • Hi Scott, they are true dates.
    – ReneZ
    Commented Mar 26, 2017 at 19:21
  • Can you show a screenshot or an example?
    – yass
    Commented Mar 26, 2017 at 19:55
  • Nov 17 Dec 17 Jan 18 Feb 18 Mar 18 0 0 0 0 0 0 0 0 0 0 75 75 75 0 75 0 0 0 0 0 0 0 0 0 0 0 0 75 0 0 0 0 0 0 0 0 0 0 0 0
    – ReneZ
    Commented Mar 26, 2017 at 20:06
  • @Yass, can't copy the info in or show the snip.....
    – ReneZ
    Commented Mar 26, 2017 at 20:10

2 Answers 2

Reset to default
0

You can use the following formula: 

=sumproduct((--(Calc1!EZ1:HR1>DATE((YEAR(Calc1!EX2)+2),1,1)))*(--(Calc1!EZ1:HR1<DATE((YEAR(Calc1!EX2)+3),1,1)))*(--(Calc1!EZ2:EZ10000<>"")),Calc1!EZ2:HR10000)

Sumproduct can sum the array reference (rows and columns) for a condition in header rows and header columns which is your case (sum the cells in the column if the header matches the condition)
all you have to do is create a criteria for rows (so the formula can define number of rows), I just used:
Calc1!EZ2:EZ10000<>"" you can create another condition for rows just to make the sumproduct recognize the rows, since the initial condition for Date will recongnize the columns, when the 2 conditions are met the cell will be add and you will have the result
Sumifs works when each criteria has the same number of rows as the other criterias, which is not the case your criteria is the Headers of columns (Calc1!EZ1:HR1) which is different from your whole array (Calc1!EZ2:HR10000)

Your question is like Superuser question 895987 and this question has the answer you need

Improve this answer
5
  • thanks, really appreciate your support. Regretfully I still get a value error with that formula. Appreciate your comment re Sumifs size.
    – ReneZ
    Commented Mar 26, 2017 at 22:11
  • I get "A value used in the formula is of the wrong data type"
    – ReneZ
    Commented Mar 26, 2017 at 22:39
  • Calc1!EZ2:EZ10000 should be number, Calc1!EZ1:HR1 custom date (mmm yy), EX2 should be date, Calc1!EZ2:HR10000 numbers
    – yass
    Commented Mar 26, 2017 at 22:49
  • I'm pulling data from another system that comes across as cell type 'General'. Thought that just multiplying it with 1 would make it a number, but that doesn't seem to be the case. :-(
    – ReneZ
    Commented Mar 26, 2017 at 23:09
  • Try to sum those numbers just by using Sum only and see if you get error or value...
    – yass
    Commented Mar 27, 2017 at 17:28
1

Hey Rene I would like to suggest some examples related to your query:

=SUMIFS(C2:C9, B2:B9, ">=10/1/2014", B2:B9, "<=10/31/2014"),

This will Sum values which falls between October 10 and October 31st.

=SUMIFS(C2:C9, B2:B9, ">="&B2, B2:B9, "<="&B7)

Above is dynamic formula where in place of date, cell address has been used. Hope this help you.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .