Fill arrays from sheet / SUMIFS / create & fill table based on multiple criteria

[Dimitris254]

Hi all,

i'm having a workbook with three sheets, "data", "arrays" and "graph". They look like so (note that this is just a selection, because my intention is to do this for more countries and categories) :

• data sheet
• arrays sheet
• graph sheet

In short, the concept is this:
- calculate some sums based on the data sheet (very basics calculations)
- create an empty table to be filled with the the calculated values and
- create a graph based on the filled table (usually stacked columns graph, but that's a story for another time).

Here are my questions so far:

====================================
1) i want to create arrays using the arrays sheet. Most of them as you can see will hold text values. I've seen the Split solution, but that would render my arrays sheet pointless, plus the code would not be as flexible (and good-looking ) as i'd like. Ideally i'd like to add / remove entries in the arrays sheet (e.g. add a new country) and the code will recognize this change in the next run.

Example:
step1: find column with header "country_array" in the arrays sheet
step2: fill county_array with the values in column, from the 2nd cell (excluding the header) to the last entry

====================================
2) what is the VBA equivalent of SUMIFS function?

For example, if i want the sales of cherries and strawberries for Norway in 2014, i would put =SUM(SUMIFS(E2:E8, A2:A8,{"cherries","strawberries"}, B2:B8,"Norway", C2:C8,2014))

====================================
3) how do i replicate this until all entries (rows) are scanned in a column? In other words, what to do when the column (range) size is unknown (or dynamic).

====================================
4) can i include this SUMIFS equivalent in a loop?

Example:
' countries array size (to be used for the counter)
countries_size = UBound(countries) - LBound(countries) + 1
For c = 1 To countries_size
SumNorway1 = SUM(SUMIFS(...B2:B8,countries(c)...)
Next c

====================================
5) as aforementioned, i'd like to create an empty table in the graph sheet, that will accept the calculated sums (as seen in graph sheet).

Example:
step1: as columns headers, put the elements of "years" array (if possible also merge the 2 cells for years as shown in the graph sheet)
step2: as sub-columns (below the years), put the elements of "semester" array
step3: as row headers, put the elements of "category" array

====================================
6) and finally, how do i write a value (e.g. the sums calculated) to a cell according to multiple criteria?

Example:
FIND the cell with column = "2014" AND sub-column = "h1" AND row = "cherries", then copy the calculated SumNorway1 to that cell.

-------
PS: Consider me as a complete noob, who knows some programming in general and now getting serious with VBA

[dflak]

It would help a lot if you post a sample spreadsheet with non-sensitive data.

Based on he description, I think it would help if you organized your data into Excel Tables. Excel tables know how many rows they contain, so they will help with dimensioning the array. Also the parts of a table are addressable by column heading name so you don't have to guess or compute what the range of data will be.