+ Reply to Thread
Results 1 to 5 of 5

Need Excel formula in Macro using Subtotal formula with If function

  1. #1
    Registered User
    Join Date
    11-19-2012
    Location
    Oklahoma City, OK
    MS-Off Ver
    Excel 2007
    Posts
    6

    Need Excel formula in Macro using Subtotal formula with If function

    All,

    I am new to macro writing - thank you for your help and expertise in advance!

    I have two columns. One is called Paid Amount column (AB) and the other called Line Amount columnn (X). I want to total the Paid Amount column with a subtotal formula, but including an IF function. If the Paid Amount cell = 0, then add the Line Amount cell instead. Otherwise, if the Paid Amount is not zero, add the Paid Amount cell. I want to do this with about 400 rows. What is the best way to do this?

    Here's my start with both formulas. How do I incorporate them? The formula will be on a different tab called Template. The Excel data where the data comes from is called Detail Orders.

    =SUBTOTAL(9,'Detail Orders'!AB:AB)
    =IF('Detail Orders'!AB:AB=0,'Detail Orders'!X:X,'Detail Orders'!AB:AB)

    Thanks!

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Need Excel formula in Macro using Subtotal formula with If function

    Is there a reason you want to use the SubTotal function?

    This could work...

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    11-19-2012
    Location
    Oklahoma City, OK
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Need Excel formula in Macro using Subtotal formula with If function

    AphaFrog,

    Thanks for your help. However, does the above formula include subtotal? I'm using subtotal because the data is filtered. For example, the data I'm trying to use this formula on is for only satisfied orders. On satisfied orders, sometimes the Paid Amount isn't available immediately so I would need to use the initial price (or Line Amount) instead, thus the IF function.

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Need Excel formula in Macro using Subtotal formula with If function

    This will sum the filtered visible values.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    11-19-2012
    Location
    Oklahoma City, OK
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Need Excel formula in Macro using Subtotal formula with If function

    Thank you AlphaFrog. I'm trying to test this and am having trouble. Would you mind converting this macro formula into a regular Excel formula so I can look at both and how they correlate? Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1