Where to adjust the factor ">2"

Razzy

Board Regular
Joined
Jul 24, 2020
Messages
106
Office Version
  1. 365
Platform
  1. Windows
This is a follow-up post on: Is this possible to make easier with the new "filter" formula? One formula, one cell?

Sample.xlsx
ABCDEFGH
1QuantityA (m)B (m)Hight (m)TypeTest1Test2
210,30,23,34F0,0170,056
3150,30,12,19O10,0100,022
410,30,53,34F0,1160,386
590,30,61,5F0,2030,304
620,30,51F0,1160,116
7120,40,81,5F0,6400,960
8320,30,61R0,2030,203
940,30,51F0,1160,116
10
11
12Bucket10test15
13
14test229
Ark1
Cell Formulas
RangeFormula
F2:F9F2=(B2:B9^2+C2:C9^2)^2
G2:G9G2=(B2:B9^2+C2:C9^2)^2*D2:D9
H12H12=SUM(ROUNDUP(SUMIFS(A2:A9,E2:E9,"F",F2#,UNIQUE(FILTER((B2:B9^2+C2:C9^2)^2,(E2:E9="F"))))/B12,0))
H14H14=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
140
Ark1
Cell Formulas
RangeFormula
H14H14=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...
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sample.xlsx
H
1625
Ark1
Cell Formulas
RangeFormula
H16H16=SUM(ROUNDUP(SUMIFS(A2:A9,E2:E9,"F",G2#,UNIQUE(FILTER((B2:B9^2+C2:C9^2)^2*D2:D9,(E2:E9="F"))),A2:A9,">2"),0))


This is it I think. I hafto adjust the SUMIFS function, not the Unique function.

Thx!
 
Upvote 0

Forum statistics

Threads
1,215,003
Messages
6,122,655
Members
449,091
Latest member
peppernaut

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top