How to do Conditional SUM function in VBA

simora

Board Regular
Joined
May 7, 2005
Messages
199
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Like this ?
Code:
Range("y2", Range("y" & Rows.Count).End(xlUp)).Offset(,1).Formula = _
    "=sumif(F$2:F$395,y2,U$2:U$395)"
 
Upvote 0
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:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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".
 
Upvote 0
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

<!-- 2008.2.723.35 -->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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top