Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
andreea_bianca24 pe Simpatie
 | Femeie
24 ani
Mures
cauta Barbat
24 - 58 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 19 ani
Postari: 2340
|
|
I have a range of data and want to make a SUMIFS spill.
So my range is raw data in A1:G23, headers included.
Now I only need 3 columns of data from it: C2:C23, D2:D23 and G2:G23. The latter is actually a spill range based on another column in my initial range, but it might as well be a simple range G2:G23.
To be able to run SUMIF, I need one spill range containing all those columns, and this is done with HSTACK.
| =HSTACK(C2:C23;D2:D23;G2#) |
This generated a spill range with 3 columns in I2#.
Now I want to do a SUMIFS on the second column of I2#, based on the unique values of the 1st column of I2#, and based on the unique values of the 3rd column of I2#.
So in M2 I have =UNIQUE(CHOOSECOLS(I2#;1))
and in N1 I have =TRANSPOSE(UNIQUE(CHOOSECOLS(I2#;3)))
And now my SUMIFS goes like this:
| =SUMIFS(INDEX(I2#;0;2);INDEX(I2#;0;1);M2#;INDEX(I2#;0;3);N1#) |
Source:
_______________________________________
|
|
| pus acum 3 saptamani |
|
|
TRaP
Moderator
Inregistrat: acum 7 ani
Postari: 892
|
|
I have a data table described as below:
- my data is stored in an Excel Table for one entire year
- it includes a month column
- the data in the Table requires to have duplicate column names sometimes - different types of waste (foils, bags, pallets etc.), but made of same material (e.g. plastic) so with the same waste code (15 01 02)
- since it is not possible to have duplicate header names in a Table header, the table header contains unique names, while the codes are located in the first row above the Table
- the month column is in text, obtained via =TEXT(tbREC[Date];"mm")
- of course, this could be obtained easier with =MONTH(tbREC[Date]) and have the months as numbers, but the above is what I am working with (file does not belong to me and I will not change the original data)
What I need:
- to summarize data by month and unique waste code
- therefore I need to summarize my data based on the month column in the table (unique months) and based on the waste code row above my table
- the columns corresponding to duplicate waste codes must be summed up under the unique waste code
I managed to build the following formula with ChatGPT that will require a user to only change the first 3 variables' data in order to basically apply to any table they want.
The formula below displays months on 12 rows, waste codes series of columns, and summed up data by code and month in the middle. All in 1 spilled range.
Note: if in your data table you have months as numbers, simply change the formula in this color to SEQUENCE(12)
=LET(
tableHeader; F28:U28;
tableMonthColumn; tbREC[Month];
tableData; tbREC[[plastic bags]:[metal]];
uniqueMonths; TEXT(SEQUENCE(12);"00");
headers; SUBSTITUTE(tableHeader;".";" ");
uniqHead; UNIQUE(headers; TRUE);
VSTACK(
HSTACK("unique -->"; uniqHead);
HSTACK(
uniqueMonths;
MAKEARRAY(
ROWS(uniqueMonths);
COLUMNS(uniqHead);
LAMBDA(r;c;
SUM(
(tableMonthColumn = INDEX(uniqueMonths;r)) *
(headers = INDEX(uniqHead;c)) *
tableData
)
)
)
)
)
) |
Since in the end I need my data in reverse - months going over 12 columns and waste codes vertically in 1 column - I simply TRANSPOSE the spill above. E.g., spill above in AE1# -> TRANSPOSE(AE1#)
Source: ChatGPT
|
|
| pus acum 6 zile |
|
|
TRaP
Moderator
Inregistrat: acum 7 ani
Postari: 892
|
|
For some of the companies I have multiple locations and there is also a location column within the table.
Since I have to do the summarization by location, I added the specific variables and included the condition in the final calculation.
I marked the added rows in green:
=LET(
Location; "XXXXXX";
tableLocationColumn; tbREC[Location];
tableHeader; F28:U28;
tableMonthColumn; tbREC[Month];
tableData; tbREC[[plastic bags]:[metal]];
uniqueMonths; TEXT(SEQUENCE(12);"00");
headers; SUBSTITUTE(tableHeader;".";" ");
uniqHead; UNIQUE(headers; TRUE);
VSTACK(
HSTACK("unique -->"; uniqHead);
HSTACK(
uniqueMonths;
MAKEARRAY(
ROWS(uniqueMonths);
COLUMNS(uniqHead);
LAMBDA(r;c;
SUM(
(tableMonthColumn = INDEX(uniqueMonths;r)) *
(headers = INDEX(uniqHead;c)) *
(tableLocationColumn = Location) *
tableData
)
)
)
)
)
) |
|
|
| pus acum 6 zile |
|