Apply SUMIFS Formula to an Array

[allenneukomm]

Hi all,

Im am trying to come up with a way to have my SUMIFS Formula loop through an array within the formula itself.
here's the code I'm using:

Please Login or Register  to view this content.

Now Energy is a String which reference which works fine, but my formula gets stuck on "Ccy".
I thought about doing a "For Each" loop but I would still need to provide the Currency Array in the formula which would take me back to square one...
If you have any pointers that would be great!

Thanks in advance.

Best,
Allen

[xlnitwit]

Hi,

First off, that isn't a formula, it is code. Secondly, you are not doing anything with the result of the calculation, so what you have posted makes little sense. Could you perhaps explain what exactly it is that you want to do? Looping through the array is perfectly feasible, by the way, but shouldn't be necessary- you do need to add a Sum function though, I imagine, and use Application.Sumifs rather than WorksheetFunction.Sumifs.

[allenneukomm]

hi xlnitwit

yes indeed - the subject is misleading.
The goal is to run through each of the currencies and skip the ones that return 0 and paste all other ones in a specific range. I want to try and do the rest on my own so I know what I am doing but I've been stuck here for a few hours now... So are you then saying that my "For Each" Loop should then work?
I attached a print screen of what i want the end result to look like.


Breakdown_Example_ExcelForum.JPG

Thanks and best,
Allen

[xlnitwit]

In that case, yes you need a loop. What did you try and what happened?

[allenneukomm]

i always get a type mismatch at the line below

Please Login or Register  to view this content.

If I change the "Ccy" to for example "AUD" I get the correct result, but let me in this case try and set up the mentioned "For Each" Loop.

[xlnitwit]

I mentioned earlier that in order to use the array you would need Application.Sumifs and not Worksheetfunction.Sumifs. However, you need a loop since you want to test each currency in turn.

[nilem]

maybe

Please Login or Register  to view this content.

Ccy(0) means "AUD"
Ccy(1) means "BRL" etc.