# Need Excel formula in Macro using Subtotal formula with If function

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

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