Results 1 to 15 of 22
Thread: sum; sumif, etc. (2000)

20040204, 13:40 #1
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
sum; sumif, etc. (2000)
Having a little trouble trying to figure out the following: On worksheet 1, in Column A, Rows 1365 are days of the year in the format "mm/dd/yy". In Column B, Rows 1365 are listed quantities of a commodity I have purchased on each of the respective days. In worksheet 2, in cells A1A12 are the months of the year in the format "mmyy". What I would like to do is put a formula in cell B1 of Worksheet 2 that will sum all of the commodities I purchased during January (from Worksheet 1). I have tried "=sumif(month('worksheet1!A1:'worksheet!A365)='wor ksheet2!A1,'worksheet1!A1:'worksheet1!A365) both as a array and nonarray, but to no avail. Any help? I tried a search, but nothing struck me as near enough to adopt.
Thanks in advance.

20040204, 14:04 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: sum; sumif, etc. (2000)
If the values in column A in both worksheets are dates, you can use something like this in B1 on Sheet2:
=SUM((DATE(YEAR(Sheet1!$A$1:$A$365),MONTH(Sheet1!$ A$1:$A$365),1)=Sheet2!A1)*Sheet1!$B$1:$B$365)
This is an array formula, i.e. it must be confirmed with Ctrl+Shift+Enter. You can then fill it down to B12. If, however, the values in column A are text values, the formula becomes slightly different.

20040204, 14:06 #3
 Join Date
 Jan 2001
 Posts
 3,788
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: sum; sumif, etc. (2000)
Try the following array formula (cell A1of Worksheet2 also need to be a date)
=SUM(Worksheet1!$B$1:$B$365*(MONTH(Worksheet1!$A$1 :$A$365)=Month(Worksheet2!$A1)))

20040204, 14:46 #4
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: sum; sumif, etc. (2000)
Thanks Hans and Tony. Inasmuch as the entries in A1:A365 are numerical in trhe format "mm/dd/yy", I have opted for Hans suggestion.

20040204, 15:16 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: sum; sumif, etc. (2000)
Another option that might not be as sluggish is to use a pivot table.
Just add a column to your wks1 data for the month [ =month(a1) or = text(A1, "mmmm") ] and copy it down the column. You could pastespecial values afterwards. Then create the pivot table with the month column and the sum of Col B.
Steve

20040205, 10:35 #6
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: sum; sumif, etc. (2000)
Hans,
Is there a limit that the formula/excel will look back to? That is, I have 40 years plus of daily prices. When I try to plug in the whole range of 40 plus years of data into the formula instead of just the years worth, I get a "#VALUE". Interpolating, I found it would only take up to 13,324 cells of data (i. e., the range is Sheet1A1:A13324). Therefore, if I want to apply this procedure to that particular file, it won't allow me to go back farther than 30 or so years. While it may not seem so, I am required to do comparisons that long ago. Any hints?
Thanks

20040205, 11:25 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: sum; sumif, etc. (2000)
I just did a test, and couldn't find a limit (except that dates in Excel start in 1900). Check that you adapted the formulas correctly when expanding the range  Excel will not necessarily do that automatically the way you want.
I managed to use a range of 100 years (over 36,000 cells in the first sheet), but recalcuting the formulas became excruciatingly slow. So it is probably wise to look for an alternative, as suggested by Steve.

20040205, 11:57 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: sum; sumif, etc. (2000)
Their is a limit (based on total size of the array) whihc shouldn't be an issue with single column ones. There is a single column limit that your column can not be the entire column. You can use from A1:A65535 or A2:A65536, but not A1:A65536 or A:A. I think I read somewhere that it is "by design".
Most times the "#value" means you forgot to confirm with ctrlshiftenter. I second Hans' comment on sluggishness (and downright "wait a full minute" to calc). Arrays use up a lot of resources since you are doing a lot of the same calcs over and over and over again. They will be especially SLOW (seemingly to HALT excel) especially after the initial copy, when excel is creating the "calcdependencyrings". Better to use a pivot table or if the output is not exactly correct a macro would work better than a hundreds of arrays looking at thousands of cells.
Formulas are generally quicker than macros, but if the formulas use 1,000 times the number of operations as the macro code, it is not surprising that the macro would be quicker.
You have posted in the past with very similar questions: The thread at <post#=315619>post 315619</post#> has an "Auto" macro to get rid of the "sluggishness" and seems to be almost identical (it was another commodity question), and the thread starting at <post#=227625>post 227625</post#> is another very similar question only dealing with temperatures from a large number of years.
You might want to review these threads to see if they offer additional insight.
Steve

20040205, 13:02 #9
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: sum; sumif, etc. (2000)
Hans/Steve,
After much consternation and number crunching, I think I have found the problem. In my original post, I indicated the numbers on Sheet 1 were fixedi. e., they were entered directly into the worksheet. As long as this is the case, or as long as the formula you guys provided refers to actual numerical entries in Sheet 1 it works fine. Where it returns a #VALUE! is if in Sheet 1 a cell that I want to sum contains a formula that returns a value that is nonnumerical. For instance, indulge me while I change the facts around a bit so that the values in Sheet 1 rang B1 to B365 are derived from a formula such as "If(CX="","",(CX+DX)/2)". Lets say further that numbers are entered in Sheet 1 Cells C1C90, and the corresponding cells in column D, but nothing in either columns C or D from Row 91365. As a result, the formula in cell B91 will return a "blank" cell, but somehow the formula you guys have come up with is reading this as text (?). At any rate, when I erase the formula in cells C91C365, your formula works fine. I would like to keep the formula in the cells in column Cany ideas?
Thanks.

20040205, 14:56 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: sum; sumif, etc. (2000)
To use SUM as Hans suggested requires that they be numbers. You could change your formula
=If(CX="",0,(CX+DX)/2)
to get around it since you are using SUM or you can put an IF in the array (confirm with ctrlshiftenter)
=SUM(IF(((DATE(YEAR(Sheet1!$A$1:$A$366),MONTH(Shee t1!$A$1:$A$366),1)=Sheet2!A1)*ISNUMBER(Sheet1!$B$1 :$B$366)),Sheet1!$B$1:$B$366))
I think the IF will make the formulas even more sluggish.
Steve

20040205, 15:14 #11
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: sum; sumif, etc. (2000)
Thanks, Steve. I had thought about the modification to the equation suggestion, but decided against it due to the fact that we might have some zero values! I like your idea about the "IF" staement, though, and have incorporated it and it seems to work quite well!
Thanks again!

20040306, 18:08 #12
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: sum; sumif, etc. (2000)
A little twist to an old issue: In addition to the above, I would like to keep running totals during a given month, and compare those running totals to budget numbers through the same date. For example, if I bought 3 items on the 1st, 4 on the 2nd, and 8 on the third, and my budget was 4, 6, and 5, respectively, I would like to keep running totals of my actual purchases to budget on the 1st, 2nd and 3rd. I would like to use as a reference the date in my formula, as I will be copying the formula over for many months. I tried the following formula as an array: =sum(if(and(month(a1:a3)=1,day(a1:a3)=1),b1:b3) where a1:a3 are the dates (the 1st, 2nd, and 3rd), and b1:b3 contained my purchases (I hadn't gotten to a comparison to budget yet), but it didn't workit returned zero.
Any help?

20040306, 20:46 #13
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: sum; sumif, etc. (2000)
In Array formulas: AND is a multiply and OR is summing. Confirm with ctrlshiftenter
<pre>=sum(if((month(a1:a3)=1)*(day(a1:a3)=1),b1:b3 )</pre>
Steve

20040406, 09:04 #14
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: sum; sumif, etc. (2000)
A slight twist: I have the following array formula we derived previously in a cell: "=SUM(IF((MONTH('HDDs2004'!$B$3:$B$368)*YEAR('HDDs2004'!$B$3:$B$368))=(MONTH('Degree Days (Raw Data)'!$A471)*YEAR($A471)),'HDDs2004'!D$3$368))". What I want is for the formula to result in a blank cell when the cells in the range 'HDDs2004'!d$3$368 are blank (i. e., e,pty). For example, if the all or any number of the cells in the range 'HDDs2004'!d$3$368 for a particular month/year are empty, I want this cell to be empty as well. Any ideas? I tried conditionaing the summing of the data in the 'HDDs2004'!d$3$368 range to be ISNUMBER('HDDs2004'!d$3$368), but it returned a zero for all months regardless if the cells were empty or not.
Thanks,
Jeff

20040406, 10:27 #15
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: sum; sumif, etc. (2000)
A cell can not be empty or blank if it has a formula. You can have it display a "null string" if desired to "appear blank"
Is this what you are after: (ARRAY formula, confirm with ctrlshiftenter):
=IF(SUM(IF(NOT(ISBLANK('HDDs2004'!D$3$368)),1))=0,"",SUM(IF((MONTH('HDDs2004'!$B$3:$B$368)*YEAR('HDDs2004'!$B$3:$B$368))=(MONTH('Degree
Days (Raw Data)'!$A471)*YEAR($A471)),'HDDs2004'!D$3$368)))
It counts the nonblanks in 'HDDs2004'!D$3$368 and if there are no "nonblanks", they must all be blank, so a null string is displayed. If there are some nonblanks then the sum you list is displayed.
Steve