Macro to Sum Columns / Exclude Certain Criteria

AlwaysLearning2018

Board Regular
Joined
Nov 23, 2018
Messages
51
Hello,

I am wondering if anyone may be able to assist me in creating a VBA macro. What I am looking to do with the below is sum up the "Long Total" column, but only where the "Long Curr" column shows USD. Anything else I need the long total column to be zeroed out. The last piece is I need to ultimately sum this up by the "Number Long" column. For example, in the "Number Long" column there are 2 entries that have 777777USD and I need both of those totals to be summed together. If 777777USD also appeared in the "Number Short" column I would need that figured in as well. I need to do the same with the "Short Total" column, where anything in the "Short Curr" column that shows USD is summed up and any other value results in the "Short Total" column being zeroed out. Again, also summing by the "Number Short" column where it is the same. Any help would be greatly appreciated!!!!


VDPort Number (Long)Number (Short)Long TotalLong CurrShort TotalShort Curr
4/2/2019111 111111CHF111111GBP37,000.00CHF29,000.00GBP
4/2/2019600 222222EUR222222USD105,000.00EUR117,000.00USD
4/2/2019222 333333HKD333333USD2,700,000.00HKD346,555.00USD
4/2/2019500 444444USD444444EUR4,000,400.00USD3,630,500.00EUR
4/2/2019111 777777USD777777HKD125USD1,100.00HKD
4/2/2019111 777777USD777777JPY51,000.00USD5,562,000.00JPY
4/2/2019222 555555USD555555ZAR210USD3,300.00ZAR
4/2/2019222 333333ZAR333333USD1,450,000.00ZAR90,000.00USD

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

Do you actually need a macro ... when a simple Sumif or Sumproduct can handle the situation ...?
 

AlwaysLearning2018

Board Regular
Joined
Nov 23, 2018
Messages
51
Hi James006,

Completely agree with you that a macro isn't necessary. Unfortunately for me the ask coming down to me is to automate via macro so no on be has to think or be accountable. Is there a macro that I could write that just zeroes out the numbers that don't have USD on the end but sums up those that do have the USD on the end?

Thanks!!
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hi,

Another shortcut ... Insert a Pivot Table ...

Hope this will help
 

AlwaysLearning2018

Board Regular
Joined
Nov 23, 2018
Messages
51
Thanks. That's the other path I'm pursuing. Any assistance on how to setup the material would be appreciated, if possible.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,068
Messages
5,545,795
Members
410,708
Latest member
SanTrapGamer
Top