# Thread: sum; sumif, etc. (2000)

1. ## sum; sumif, etc. (2000)

Having a little trouble trying to figure out the following: On worksheet 1, in Column A, Rows 1-365 are days of the year in the format "mm/dd/yy". In Column B, Rows 1-365 are listed quantities of a commodity I have purchased on each of the respective days. In worksheet 2, in cells A1-A12 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 non-array, but to no avail. Any help? I tried a search, but nothing struck me as near enough to adopt.

2. ## 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.

3. ## 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)))

4. ## 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.

5. ## 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 paste-special values afterwards. Then create the pivot table with the month column and the sum of Col B.

Steve

6. ## 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

7. ## 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.

8. ## 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 ctrl-shift-enter. 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 "calc-dependency-rings". 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

9. ## 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 fixed-i. 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 non-numerical. 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 C1-C90, and the corresponding cells in column D, but nothing in either columns C or D from Row 91-365. 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 C91-C365, your formula works fine. I would like to keep the formula in the cells in column C-any ideas?
Thanks.

10. ## 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 ctrl-shift-enter)

=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

11. ## 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!

12. ## 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 work-it returned zero.
Any help?

13. ## Re: sum; sumif, etc. (2000)

In Array formulas: AND is a multiply and OR is summing. Confirm with ctrl-shift-enter

<pre>=sum(if((month(a1:a3)=1)*(day(a1:a3)=1),b1:b3 )</pre>

Steve

14. ## Re: sum; sumif, etc. (2000)

A slight twist: I have the following array formula we derived previously in a cell: "=SUM(IF((MONTH('HDDs-2004'!\$B\$3:\$B\$368)*YEAR('HDDs-2004'!\$B\$3:\$B\$368))=(MONTH('Degree Days (Raw Data)'!\$A471)*YEAR(\$A471)),'HDDs-2004'!D\$3\$368))". What I want is for the formula to result in a blank cell when the cells in the range 'HDDs-2004'!d\$3\$368 are blank (i. e., e,pty). For example, if the all or any number of the cells in the range 'HDDs-2004'!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 'HDDs-2004'!d\$3\$368 range to be ISNUMBER('HDDs-2004'!d\$3\$368), but it returned a zero for all months regardless if the cells were empty or not.
Thanks,
Jeff

15. ## 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 ctrl-shift-enter):

=IF(SUM(IF(NOT(ISBLANK('HDDs-2004'!D\$3\$368)),1))=0,"",SUM(IF((MONTH('HDDs-2004'!\$B\$3:\$B\$368)*YEAR('HDDs-2004'!\$B\$3:\$B\$368))=(MONTH('Degree
Days (Raw Data)'!\$A471)*YEAR(\$A471)),'HDDs-2004'!D\$3\$368)))

It counts the non-blanks in 'HDDs-2004'!D\$3\$368 and if there are no "non-blanks", they must all be blank, so a null string is displayed. If there are some non-blanks then the sum you list is displayed.

Steve

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•