Like this ?
Code:Range("y2", Range("y" & Rows.Count).End(xlUp)).Offset(,1).Formula = _ "=sumif(F$2:F$395,y2,U$2:U$395)"
How can I impliment the Conditional SUM function in VBA?
I need to go down a list of cells in Column Y and do a conditional SUM on each of the unique items listed and put the totals next to each in column Z. which is where I would normally put this on a worksheet.
=SUM(IF(F2:F395="Printer Supplies",U2:U395,0))
Printer Supplies is at Y2 and I need to do all the cells in Y as in
Range("Y2", Range("Y65536").End(xlUp)).Select
Is there a way that you can write a Conditional SUM formula like the one that I have below using VBA ? The worksheet function does not incriment correctly with the Fill Down command anyway.
In this case Printer Supplies is at Y2, so how can I do the equivalent of
{=SUM(IF(F2:F395=Y2,U2:U395,0))}
in VBA and have it go down to the last used cell in that column?
Any code or ideas welcome.
Thanks
Like this ?
Code:Range("y2", Range("y" & Rows.Count).End(xlUp)).Offset(,1).Formula = _ "=sumif(F$2:F$395,y2,U$2:U$395)"
Thanks. It worked great.
If it is arrayFormular we should change:
Range("y2", Range("y" & Rows.Count).End(xlUp)).Offset(,1).FormulaArray = _
"=sumif(F$2:F$395,y2,U$2:U$395)"
more quickly, after the above statement, we should change to
PHP Code:
With Range("y2", Range("y" & Rows.Count).End(xlUp)).Offset(,1)
.FormulaArray = "=sumif(F$2:F$395,y2,U$2:U$395)"
.Caculate
.Value=.Value
End With
Hi tigertiger
Remarks:
- the Sumif() formula is not an array formula
- if it was an array formula then you should not write the formula using the A1 notation.
From the help:
If you use this property to enter an array formula, the formula must use the R1C1 reference style, not the A1 reference style
Kind regards
PGC
To understand recursion, you must understand recursion.
Hi pgc01,
Just a point of interest on the range object's formulaarray property: the helpfile is incorrect on that - using A1 notation is acceptable. I remember testing this on Excel 2000 through to Excel 2007 without any issues - I don't have Excel 97 to test it on but perhaps that note in the helpfile was valid prior to Excel 2000? If anyone has Excel 97 to check it then I'd be interested to know...
Colin
Hi Collin
This is a problem that resurfaces now and then. Although most of the times you can use the A1 notation with the FormulaArray property, there have been cases where it doesn't (if I recall well some of them posted in this board). I have had cases where it didn't work with the A1 reference style and I really had to use the R1C1 reference style. That'y why I always use and recommend the R1C1 notation with the FormulaArray.
I'm sorry that I don't have any examples to back this up. Without a clear case where it doesn't work with the A1 notation you can always wonder whether it was not just a user error and not a problem of reference style. I tried to find one of those examples but, unfortunately, could not find them. If I find any in the next days I'll post them.
It's safe, however, to always recommend the use of the R1C1 notation with the FormulaArray property. I have excel 2000 and 2007 installed and in both that's what the help says that must be done. One would expect that, with so many versions in between, that would have been corrected if it was wrong (but it could happen). The wording in the help is very clear "... the formula must use the R1C1 reference style, not the A1 reference style".
Kind regards
PGC
To understand recursion, you must understand recursion.
Hi pgc01,
It'd be good if you find some examples. I have used A1 notation quite extensively in Excel XP through to 2007 without issue, which is why I presumed that the note in the helpfile applied to an older version and had not been updated. When I researched this topic previously, I found comments by other users that the helpfile was incorrect, which supported my own findings. But if there are exceptions, then it would be very good to know them.I'm sorry that I don't have any examples to back this up. Without a clear case where it doesn't work with the A1 notation you can always wonder whether it was not just a user error and not a problem of reference style. I tried to find one of those examples but, unfortunately, could not find them. If I find any in the next days I'll post them.
In addition, please see the new content comment here at the MSDN.Microsoft.com site which also confirms my understanding that the information in the helpfile is incorrect.
http://msdn.microsoft.com/en-us/library/bb208529.aspx
ColinThe FormulaArray property will accept formulas written in A1 referencing style even though the documentation claims otherwise. This is true for Excel 97 through 2007.
Last edited by Colin Legg; Nov 17th, 2008 at 06:19 AM.
Colin, thank you for the link.
I'm sure I had cases where it didn't work with the A1 reference style and then worked in R1C1. It may, however, happened that it was due to something I did wrong, and not because of the notation. If I find anything I'll post it.
Kind regards
PGC
To understand recursion, you must understand recursion.
I'm dealing with a similar problem and haven't found an answer to that either.
My problem is that I want to have the conditional sum in a specific cell (say, A1) but written as a normal sum. An example to clarify:
[C343:HD343] looks like this:
1, 2, 3, ..., 15,1, 2, ... 15, ...
Now I want to sum up all the values in a row with the same dimensions, say [C344:HD344] when the value of [C343:HD343] equals a specific number, but I want the "SUM" formula in the cell, not the actual result.
Like this
1 2 3 1 2 3 1 2 3
4 5 7 2 4 3 2 3 4
Now I want to sum up (eg in cell A1) the values of the row underneath but only when the cell in the upper row equals 2.
The result will be 5+4+3=12 but I need to have =SUM(...) on my worksheet (not SUMIF!)
Is there a way to do this?
I tried something likeBut it obviously doesn't work.Code:Range("A1").Formula = "=SUM(" & if Cells(343,3+i)=2 then ...
Any help would be really appreciated
Like this thread? Share it with others