# 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. ## 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. ## 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. ## Re: How to do Conditional SUM function in VBA

Thanks. It worked great.

4. ## Re: How to do Conditional SUM function in VBA

Originally Posted by jindon
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=.ValueEnd With  ```

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

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

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

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

10. ## Re: How to do Conditional SUM function in VBA

Originally Posted by jindon
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

#### Posting Permissions

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