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 Miluta pe Simpatie.ro
 | Femeie
24 ani
Braila
cauta Barbat
30 - 48 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 19 ani
Postari: 2339
|
|
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 2 zile |
|