# Formula in macro

#### sujittalukde

##### Well-known Member
I am running a macro which calculated subtotal and total but the GRAND TOTAL is taking the subtotal figure also. So I wnat to set the GRAND TOTAL formula in the macro itself so that GRAND TOTAL comes corrctly?

A picture is attached to clarify the matter.
Book8
ABCD
1A2
2B3
3C4
4AA5
5D6
6E7
7F8
8AA9
9G10
10H11
11I12
12AA13
13GRAND TOTAL63=((SUM(B1:B12))-(SUMIF(A1:B12,"aa",B1:B12)))
14This calculation has to be made in macro
Sheet1

How this can be done?

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### njimack

##### Well-known Member
Don't use SUM or SUMIF - use SUBTOTAL...

=SUBTOTAL(9,B1:B12)

#### sujittalukde

##### Well-known Member
THe macro I have use the sum function. I have made some modification to it so the GRAND TOTAL is changing So I need to change the formula of Grand Total

#### ravishankar

##### Well-known Member
Hi
Try
Grand total =(SUM(B1:B12)-SUMIF(A1:A12,"aa",B1:B12))
ravi

#### sujittalukde

##### Well-known Member
This is what I am already shown in my first post. This i wnat to set in the macro. HOw can I do it?

#### VoG

##### Legend
Perhaps

Code:
``Range("B13").Formula = "=SUM(B1:B12)-SUMIF(A1:A12,""aa"",B1:B12)"``

Replies
3
Views
110
Replies
0
Views
154
Replies
0
Views
187
Replies
5
Views
156
Replies
1
Views
380

1,186,916
Messages
5,960,554
Members
438,484
Latest member
supermon

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

### Which adblocker are you using?

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

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