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

  • Are your date headers true dates or are they test that look like dates? – Scott Craner Mar 26 '17 at 18:16
  • Hi Scott, they are true dates. – ReneZ Mar 26 '17 at 19:21
  • Can you show a screenshot or an example? – yass Mar 26 '17 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 Mar 26 '17 at 20:06
  • @Yass, can't copy the info in or show the snip..... – ReneZ Mar 26 '17 at 20:10
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|||||
  • thanks, really appreciate your support. Regretfully I still get a value error with that formula. Appreciate your comment re Sumifs size. – ReneZ Mar 26 '17 at 22:11
  • I get "A value used in the formula is of the wrong data type" – ReneZ Mar 26 '17 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 Mar 26 '17 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 Mar 26 '17 at 23:09
  • Try to sum those numbers just by using Sum only and see if you get error or value... – yass Mar 27 '17 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|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.