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:
loca23 la Simpatie.ro
 | Femeie
23 ani
Cluj
cauta Barbat
23 - 44 ani |
|
TonyTzu
Moderator
 Inregistrat: acum 12 ani
Postari: 252
|
|
Assuming you want to sum some values based on a criteria and on filtered rows, here is the formula:
=SUMPRODUCT(SUBTOTAL(109;OFFSET($J$54;ROW($J$54:$J$358)-ROW($J$54);;1));--($A$54:$A$358 = L34))
where:
- in J54:J358 I have the values I want to SUM
- in A54:A358 I have the month numbers (1 for Jan, 2 for Feb etc.)
- in L34 I have month number for which I want subtotal
Formula above is added to M34 and dragged down to get all 12 months.
|
|
| pus acum 6 ani |
|
TonyTzu
Moderator
Inregistrat: acum 12 ani
Postari: 252
|
|
Sum visible rows in a filtered list
Source:
=SUBTOTAL(9;range)
Following the example above, to sum cells in column F for visible rows only, use:
=SUBTOTAL(9;F5:F14)
If you are hiding rows manually (i.e. right-click + Hide), use this version instead:
=SUBTOTAL(109;F5:F14)
By changing the function number, the SUBTOTAL function can perform many other calculations (e.g. COUNT, SUM, MAX, MIN, etc.). See the full list of function numbers on this page:
|
|
| pus acum 6 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 739
|
|
Calculate from selection with multiple conditions:
=SUMPRODUCT(SUBTOTAL(109;OFFSET($L$4;ROW($L$4:$L$5000)-ROW($L$4);;1));--($J$4:$J$5000=$S70);--($D$4:$D$5000=Y$69))
Condition 1 - --($J$4:$J$5000=$S70)
Condition 2 - --($D$4:$D$5000=Y$69)
You can add more.
|
|
| pus acum 5 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2186
|
|
Sum in visible cells (after filter) but ignore errors with the following array formula:
=SUM(IF(SUBTOTAL(2;OFFSET(V24;ROW(Table1[Salaries])-ROW(V24);0));Table1[Salaries]))
Confirm with CTRL + SHIFT + ENTER
_______________________________________
|
|
| pus acum 10 luni |
|