Hello,
Hoping for some help with this:
=SUMIFS('2007-2014-15 stats'!D:H,'2007-2014-15 stats'!C:C,">=1/1/2015",'2007-2014-15 stats'!C:C,"<2/1/2015")
I tried entering as an array, and that isn't working. Columns have text headers so I am not sure SUMPRODUCT will work either and I'd like to not have to specify rows as the sheet continues to grow.
Trying to sum the values in columns D to H depending on the date value (by month) in column C.
Thanks.
May be:
Please Login or Register to view this content.
Quang PT
That's giving me a value error, probably because of the text headers on the columns?
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
I tried to omit the rows with text in the ranges and I got a "0" value when using sumproduct.
I will upload a book, give me minute as the one I am working off is huge, so I will have to recreate.
Thanks!
Yes, there cannot be any text values in the Sum RangeOriginally Posted by source
Use Defined ranges like D2:H100
It's very poor performance anyway to use entire column references with sumproduct.
See attached
I tried specifying a range with the SUMPRODUCT and got 0 with bebo's suggestion.
I am trying to use entire column references because of the size of the data and the fact that the data will continue to grow for a long period of time.
Again, use defined ranges.
I see the data in the other sheet goes from row 6 to 66
also, unlike Sumif(s), Sumproduct doesn't convert the text string "1/1/2015" to a useable date.
It literally treats it like a text string.
You have to convert that string to a date, commonly done by adding 0
Try
=SUMPRODUCT(('2007-2014-15 stats'!$C$6:$C$69>="1/1/2015"+0)*('2007-2014-15 stats'!$C$6:$C$69<"2/1/2015"+0)*('2007-2014-15 stats'!$D$6:$H$69))
Look into using Dynamic Named Ranges
http://www.contextures.com/xlNames01.html
I should also mention I inherited this mess, and am trying to fix a number of issues with formulas in the sheet and simplify things without totally starting from scratch so that the users can continue to update their stats without having to be retrained from how they learned it.
I get to reinvent the wheel in the coming fiscal year and will do away with this nonsense and will be generating stats with pivot tables and pivot charts. Thank goodness.
will SUMPRODUCT work if I use the DATE function? My first attempts were using the DATE function, ">="&DATE(1,1,2015)
You don't need the concatenation with the quotes like you do in sumifs
Try just
Range>=DATE(...)
thanks for all the replies, so is it possible to use SUMIFS at all?
basically, I would have to use a dynamic range to use SUMIF?
So I would have to create a named table from D5:H? and then use in the formula like
=SUMIFS(namedtable,'2007-2014-15 stats'!C:C,">=1/1/2015",'2007-2014-15 stats'!C:C,"<2/1/2015") or would I used =SUMIFS(namedrange....
how do I make the named table infinite?
Sorry if I'm sounding dumb here, I'm just trying to wrap my head around using a dynamic range.
=SUMPRODUCT(('2007-2014-15 stats'!$C$6:$C$69>=DATE(1,1,2015)*('2007-2014-15 stats'!$C$6:$C$69<DATE(2,1,2015)*('2007-2014-15 stats'!$D$6:$H$69))))
This gave me 0... weird.
Sumifs Can't do it because it requires all the ranges (the sum range, and the criteria ranges) to be the same size/shape.
Your criteria are in 1 column Each, but the range you're summing is 5 columns.
No, you don't HAVE to use named ranges.
However, there cannot be any TEXT values in the Sum Range, therefor you can't use entire columns (because you have headers in row 1 presumably)
So you have to specify row #s in your ranges C2:C100.
You could do C2:C1048576
That is pretty much an entire column, minus row 1.
However, also unlike Sumifs, sumproduct processes the ENTIRE range (sumifs only processes rows within the sheet's effectively used range)
So doing entire columns creates ALOT of unnecessary work for Excel (by processing thousands of blank rows), resulting in poor performance.
The best solution is to use Dynamic Named Ranges. These are able to adjust by themselves as your data grows.
So you don't have to hardcode row#s in each range.
You would create 2 named ranges.
1 for the D:H range, 1 for column C
Then your formula is
=SUMPRODUCT((name1forC>="1/1/2015"+0)*(name1forC<"2/1/2015"+0)*(name2forDtoH))
Last edited by Jonmo1; 01-26-2015 at 02:01 PM.
Misplaced parens...
Try
=SUMPRODUCT(('2007-2014-15 stats'!$C$6:$C$69>=DATE(1,1,2015))*('2007-2014-15 stats'!$C$6:$C$69<DATE(2,1,2015))*('2007-2014-15 stats'!$D$6:$H$69))
I got there, too - but fractionally too late. This might be more user-friendly, though. It picks up the dates from B16-27.
The bottom line is that SUMRODUCT doesn't like dates entered as dates... Hard code them (as Jonmo did) or refer to them in another cell (as I did).Formula:
Please Login or Register to view this content.
Okay,
So easiest solution I found was to make a total column (I) adding up the rows in columns D to H, This allows me to use SUMIF. Perhaps it's inelegant, but seems the easiest.
=SUMIFS('2007-2014-15 stats'!I:I,'2007-2014-15 stats'!C:C,">=1/1/2015",'2007-2014-15 stats'!C:C,"<2/1/2015")
I wouldn't call that inelegant at all.
In fact, it's probably an even better solution (I probably should have suggested it in the first place).
That's called a 'Helper Column'
And those are not a bad thing, because they 'help'.
Excel gives you over 16000 columns, there's nothing wrong with using a few of them.
Also, I find that 9 times out of 10, after creating a formula that does all the intermidiate calculations in 1 formula..
Shortly after I realize, hey it sure would be nice to also see those intermediate calculations as well.
So the results of SUM(D2:H2) and SUM(D3:H3) and SUM(D4:H4) etc.., might be usefull information later down the line in your sheet anyway.
Last edited by Jonmo1; 01-26-2015 at 02:23 PM.
Thanks for all your help! I learned lots.
Like I said, I inherited this mess... I see they used a total column (Q) for pages, so why they didn't put one for documents is beyond me.
Last edited by source; 01-26-2015 at 02:27 PM.
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks