Macro to Sum Columns / Exclude Certain Criteria

[AlwaysLearning2018]

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!!!!


[TABLE="width: 914"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]VD[/TD]
[TD]Port[/TD]
[TD] Number (Long)[/TD]
[TD]Number (Short)[/TD]
[TD]Long Total[/TD]
[TD]Long Curr[/TD]
[TD]Short Total[/TD]
[TD]Short Curr[/TD]
[/TR]
[TR]
[TD="align: right"]4/2/2019[/TD]
[TD="align: right"]111 [/TD]
[TD]111111CHF[/TD]
[TD]111111GBP[/TD]
[TD="align: right"]37,000.00[/TD]
[TD]CHF[/TD]
[TD="align: right"]29,000.00[/TD]
[TD]GBP[/TD]
[/TR]
[TR]
[TD="align: right"]4/2/2019[/TD]
[TD="align: right"]600 [/TD]
[TD]222222EUR[/TD]
[TD]222222USD[/TD]
[TD="align: right"]105,000.00[/TD]
[TD]EUR[/TD]
[TD="align: right"]117,000.00[/TD]
[TD]USD[/TD]
[/TR]
[TR]
[TD="align: right"]4/2/2019[/TD]
[TD="align: right"]222 [/TD]
[TD]333333HKD[/TD]
[TD]333333USD[/TD]
[TD="align: right"]2,700,000.00[/TD]
[TD]HKD[/TD]
[TD="align: right"]346,555.00[/TD]
[TD]USD[/TD]
[/TR]
[TR]
[TD="align: right"]4/2/2019[/TD]
[TD="align: right"]500 [/TD]
[TD]444444USD[/TD]
[TD]444444EUR[/TD]
[TD="align: right"]4,000,400.00[/TD]
[TD]USD[/TD]
[TD="align: right"]3,630,500.00[/TD]
[TD]EUR[/TD]
[/TR]
[TR]
[TD="align: right"]4/2/2019[/TD]
[TD="align: right"]111[/TD]
[TD] 777777USD[/TD]
[TD]777777HKD[/TD]
[TD="align: right"]125[/TD]
[TD]USD[/TD]
[TD="align: right"]1,100.00[/TD]
[TD]HKD[/TD]
[/TR]
[TR]
[TD="align: right"]4/2/2019[/TD]
[TD="align: right"]111 [/TD]
[TD]777777USD[/TD]
[TD]777777JPY[/TD]
[TD="align: right"]51,000.00[/TD]
[TD]USD[/TD]
[TD="align: right"]5,562,000.00[/TD]
[TD]JPY[/TD]
[/TR]
[TR]
[TD="align: right"]4/2/2019[/TD]
[TD="align: right"]222[/TD]
[TD] 555555USD[/TD]
[TD]555555ZAR[/TD]
[TD="align: right"]210[/TD]
[TD]USD[/TD]
[TD="align: right"]3,300.00[/TD]
[TD]ZAR[/TD]
[/TR]
[TR]
[TD="align: right"]4/2/2019[/TD]
[TD="align: right"]222[/TD]
[TD] 333333ZAR[/TD]
[TD]333333USD[/TD]
[TD="align: right"]1,450,000.00[/TD]
[TD]ZAR[/TD]
[TD="align: right"]90,000.00[/TD]
[TD]USD[/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hello,

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

 

[AlwaysLearning2018]

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]

Hi,

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

Hope this will help

 

[AlwaysLearning2018]

Thanks. That's the other path I'm pursuing. Any assistance on how to setup the material would be appreciated, if possible.

 

[James006]

how to setup the material

??? ... What do you mean ... ???