SUMIFS in VBA

[TG2812]

Hi, can anyone explain me how to translate this formula excel into VBA?

=SUMIFS($H:$H,$A:$A,$A2,$B:$B,$B2,$D:$D,$D2,$E:$E,$E2)

Thanks in advance for your help.

 

[Domenic]

Re: SUMIFS in VBA Help needed please

Can you explain in a few short words what you're trying to do?

 

[TG2812]

Re: SUMIFS in VBA Help needed please

- I'm trying to track all changes that occur in worksheet "Main" and document them in worksheet "Tracking"
- Once old value and new value are reported, I'm calculating the change (in value) and the remaining balance for which i need the sumproduct formula
- The SumProduct sums all the changes in transit (column H) for a given product (column A), warehouse (Column D) and date (Column E) and returns a surplus or a deficit (see example below)

Example: For product1, region 1, warehouse1 and December 2018, I add 5 days of transit. My deficit will be -5.
Now, for product1, region2, warehouse1 and December 2018, I add 6 days of transit. My deficit will be -5 + 6 = 1

Hope this clarifies.

Thanks again for your help.

 

[Domenic]

Re: SUMIFS in VBA Help needed please

I meant as it relates to your variable rw. In any case, it looks like rw defines the last row for your ranges, so maybe...

dblMyVal = Evaluate("SUMPRODUCT(($H$2:$H$" & rw & ")*($A$2:$A$" & rw & "=A2)*($B$2:$B$" & rw & "=B2)*($D$2:$D$" & rw & "=D2)*(MONTH($E$2:$E$" & rw & ")=MONTH(E2))*(YEAR($E$2:$E$" & rw & ")=YEAR(E2)))")

 

[TG2812]

Re: SUMIFS in VBA Help needed please

Hi Domenic, Joe4,

I tried the below formula but nothing's changing. Is there something in the whole code I need to adjust cause I really do not know why this is not working..
Thanks in advance.

dblMyVal = Evaluate("SUMPRODUCT(($H:$H)*($A:$A=A" & rw & ")*($D:$D=D" & rw & ")*(MONTH($E:$E)=MONTH(E" & rw & "))*(YEAR($E:$E)=YEAR(E" & rw & ")))")

 

[TG2812]

Re: SUMIFS in VBA Help needed please

If you have any other suggestions, please let me know.

 

[Domenic]

Re: SUMIFS in VBA Help needed please

Make sure that Column E, along with all other cells that contain dates, contain true date values. You can test whether a cell contains a true date value by using the following worksheet formula...

=ISNUMBER(E2)

The formula will return True if it's a true date value. Otherwise it will return False.

 

[TG2812]

Re: SUMIFS in VBA Help needed please

It returns TRUE...

 

[Domenic]

Re: SUMIFS in VBA Help needed please

In looking at your code that you posted earlier, I see that you haven't referred to your worksheet Tracking for the ranges. As a result, the references will refer to the active sheet. So this is probably one issue. Another issue might be that while rw refers to the last used row in Column A of Tracking, you're using it to define the criteria instead of the ranges. Is this what you in fact want?

 

[TG2812]

Re: SUMIFS in VBA Help needed please

For point #1 : do you have an example to illustrate?
For point #2 : the variable rw helps to define the criteria instead of the ranges. This is correct.

 

[Domenic]

Re: SUMIFS in VBA Help needed please

For point #1 : do you have an example to illustrate?

I simply meant that since you haven't referred to a specific worksheet, the ranges will refer to the active worksheet. I don't know whether this is by design, or whether you actually mean to refer to your Tracking worksheet. If the latter, you should include a reference to your worksheet Tracking when building your string to define your formula.