Results 1 to 10 of 10

How to do Conditional SUM function in VBA

This is a discussion on How to do Conditional SUM function in VBA within the Excel Questions forums, part of the Question Forums category; How can I impliment the Conditional SUM function in VBA ? I need to go down a list of cells ...

  1. #1
    Board Regular
    Join Date
    May 2005
    Posts
    195

    Default How to do Conditional SUM function in VBA

    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

  2. #2
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995

    Default Re: How to do Conditional SUM function in VBA

    Like this ?
    Code:
    Range("y2", Range("y" & Rows.Count).End(xlUp)).Offset(,1).Formula = _
        "=sumif(F$2:F$395,y2,U$2:U$395)"

  3. #3
    Board Regular
    Join Date
    May 2005
    Posts
    195

    Default Re: How to do Conditional SUM function in VBA

    Thanks. It worked great.

  4. #4
    New Member
    Join Date
    Nov 2008
    Location
    The Earth
    Posts
    46

    Default Re: How to do Conditional SUM function in VBA

    Quote Originally Posted by jindon View Post
    Like this ?
    Code:
    Range("y2", Range("y" & Rows.Count).End(xlUp)).Offset(,1).Formula = _
        "=sumif(F$2:F$395,y2,U$2:U$395)"
    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 

  5. #5
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,682

    Default Re: How to do Conditional SUM function in VBA

    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.

  6. #6
    MrExcel MVP
    Like totally RAD man
    Colin Legg's Avatar
    Join Date
    Feb 2008
    Location
    UK
    Posts
    3,402

    Default Re: How to do Conditional SUM function in VBA

    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

  7. #7
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,682

    Default Re: How to do Conditional SUM function in VBA

    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.

  8. #8
    MrExcel MVP
    Like totally RAD man
    Colin Legg's Avatar
    Join Date
    Feb 2008
    Location
    UK
    Posts
    3,402

    Default Re: How to do Conditional SUM function in VBA

    Hi pgc01,

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

    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

    The FormulaArray property will accept formulas written in A1 referencing style even though the documentation claims otherwise. This is true for Excel 97 through 2007.
    Colin
    Last edited by Colin Legg; Nov 17th, 2008 at 06:19 AM.

  9. #9
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,682

    Default Re: How to do Conditional SUM function in VBA

    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.

  10. #10
    New Member
    Join Date
    Jun 2011
    Posts
    37

    Default Re: How to do Conditional SUM function in VBA

    Quote Originally Posted by jindon View Post
    Like this ?
    Code:
    Range("y2", Range("y" & Rows.Count).End(xlUp)).Offset(,1).Formula = _
        "=sumif(F$2:F$395,y2,U$2:U$395)"
    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 like
    Code:
    Range("A1").Formula = "=SUM(" & if Cells(343,3+i)=2 then ...
    But it obviously doesn't work.

    Any help would be really appreciated

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com