This is a follow-up post on: Is this possible to make easier with the new "filter" formula? One formula, one cell?
Question: How to set "test2" to only count the number if it is over 2?
Like:
*I adjusted the UNIQUE formula, and added a: "and", but it did not work. Hm...
Sample.xlsx | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | Quantity | A (m) | B (m) | Hight (m) | Type | Test1 | Test2 | |||
2 | 1 | 0,3 | 0,2 | 3,34 | F | 0,017 | 0,056 | |||
3 | 15 | 0,3 | 0,1 | 2,19 | O1 | 0,010 | 0,022 | |||
4 | 1 | 0,3 | 0,5 | 3,34 | F | 0,116 | 0,386 | |||
5 | 9 | 0,3 | 0,6 | 1,5 | F | 0,203 | 0,304 | |||
6 | 2 | 0,3 | 0,5 | 1 | F | 0,116 | 0,116 | |||
7 | 12 | 0,4 | 0,8 | 1,5 | F | 0,640 | 0,960 | |||
8 | 32 | 0,3 | 0,6 | 1 | R | 0,203 | 0,203 | |||
9 | 4 | 0,3 | 0,5 | 1 | F | 0,116 | 0,116 | |||
10 | ||||||||||
11 | ||||||||||
12 | Bucket | 10 | test1 | 5 | ||||||
13 | ||||||||||
14 | test2 | 29 | ||||||||
Ark1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2:F9 | F2 | =(B2:B9^2+C2:C9^2)^2 |
G2:G9 | G2 | =(B2:B9^2+C2:C9^2)^2*D2:D9 |
H12 | H12 | =SUM(ROUNDUP(SUMIFS(A2:A9,E2:E9,"F",F2#,UNIQUE(FILTER((B2:B9^2+C2:C9^2)^2,(E2:E9="F"))))/B12,0)) |
H14 | H14 | =SUM(ROUNDUP(SUMIFS(A2:A9,E2:E9,"F",G2#,UNIQUE(FILTER((B2:B9^2+C2:C9^2)^2*D2:D9,(E2:E9="F")))),0)) |
Dynamic array formulas. |
Question: How to set "test2" to only count the number if it is over 2?
Like:
Sample.xlsx | |||
---|---|---|---|
H | |||
14 | 0 | ||
Ark1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
H14 | H14 | =SUM(ROUNDUP(SUMIFS(A2:A9,E2:E9,"F",G2#,UNIQUE(FILTER((B2:B9^2+C2:C9^2)^2*D2:D9,AND((E2:E9="F"),A2:A9>2)))),0)) |
*I adjusted the UNIQUE formula, and added a: "and", but it did not work. Hm...