Yep, I was checking it and got it working! The correct separator is a backslash! While at work it is a colon %-) Sorry, my bad
For the record, here is a recipe to check correct delimeters:
In VBA IDE go to the immediate window and execute one by one...
Uhhm, I think it is not that easy. If you look closely, I changed all commas into semicolons before amending it. BTW in Russian locale it is colon that separates rows.
When I posted last time I just forgot to change delimeters back.
What I get from F9 with your formula...
Luke,
Your formula produces #N/A, as Index part and item part are arrays of different dimentions.
I had to amend it into =SUMPRODUCT(INDEX($B$2:$E$8;;(COLUMN(A$1)-1)*3+1)*(($A$2:$A$8={"Item1"})+($A$2:$A$8={"Item4"})+($A$2:$A$8={"Item7"})))
Thank you for participation.
Meanwhile my...
Thanks. Cannot test it now. Is it possible to change the second condition into something like ROW ($A$2:$A$8)={1,3,5} or somehow fix row numbers? Item names are too long for formula.
BTW do you know how can I get rid of SUM in...
If I wrap SUM(INDIRECT("R"&{21,23,27}&"C"&{15,15,15},FALSE)) into SUMPRODUCT, the formula sums up all three. But I still have to manually enter column coordinates.
Hi,
What I have:
Two tables. The first one (Table 1) is the origin of data: from left to right it is monthly budgets, three columns per month; from top to bottom it is a list of unique items. Columns have unique titles.
Like this:
Items Jan1 Jan2 Jan3 Feb1 Feb2 Feb3 ...