Test

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,493
Office Version
  1. 365
Platform
  1. Windows
21 01 12.xlsm
CD
21
3Name 1
11
12
13
145
15Name 9
Show Hide Rows


21 01 12.xlsm
ABCDEFGHIJKLMNOPQ
1AQQQQQExamQHQQQExamSum for QSum Non-Blank, numeric - small kk
2
3P5835188H219542541
4P9959612H7847355
5A0333251H983232363
SUMS
Cell Formulas
RangeFormula
O3:O5O3=SUMIF(A$1:M$1,"Q",A3:M3)
P3:P5P3=SUM(A3:M3)-SUM(SMALL(A3:M3,ROW(INDEX(P:P,1):INDEX(P:P,Q3))))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Some videos you may like

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,493
Office Version
  1. 365
Platform
  1. Windows
2_0 Test.xlsx
ABCHK
1NameAmountHidden columns
2Tom14to the right
3Jen21
4Tom33of this one
5Jen34
6Tom23x
7
8Number
982
108
116
125
131
14Value704
15253
1623Sum of numbers < 510
1722
18
19
20Data ValidationA2, A9
21Conditional FormattingB2:B6
22Array FormulaK16
23Formula with Named rangeB14
24Rows 3 & 5 hidden
25Columns D:G, I:J hidden
Sample
Cell Formulas
RangeFormula
B14B14=SUM(Value)
K16K16=SUM(IF(K9:K15<5,K9:K15))
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
Value=Sample!$A$14:$A$17B14
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:B6Expression=AND(B2>=20,B2<30)textYES
B2Cell Value<20textYES
Cells with Data Validation
CellAllowCriteria
A2ListTom,Jen
A9Whole numberbetween 0 and 10
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,789
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

IsaiahMaximoHayes
0​
0​
AriGiovanniDerekAyden
0​
 

SteveP29

New Member
Joined
Jul 1, 2007
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Applications Log (Fiddle Copy 2).xlsx
ABCD
1Week Int/extDate of ApplicationYear
23Int15/04/20202029
33Ext16/04/20202029
43Ext16/04/20202029
53Int 15/04/20202029
64Int 20/04/20202029
74Int 20/04/20202029
84Int 20/04/20202029
94Int 20/04/20202029
104Int 20/04/20202029
113Int 17/04/20202029
124Int 22/04/20202029
134Int 22/04/20202029
144Int 20/04/20202029
154Int 22/04/20202029
164Ext22/04/20202029
174Int24/04/20202029
184Ext24/04/20202029
194Ext24/04/20202029
205Int27/04/20202029
Data
Cell Formulas
RangeFormula
D2:D20D2=LOOKUP(2,1/(Validations!$A$2:$A$11>=[@[Date of Application]])/(Validations!$B$2:$B$11<=[@[Date of Application]]),(Validations!$C$2:$C$11))


Applications Log (Fiddle Copy 2).xlsx
ABCD
1Date FromDate ToYear
201/04/202001/04/202101/04/2020
301/04/202101/04/202201/04/2021
401/04/202201/04/202301/04/2022
501/04/202301/04/202401/04/2023
601/04/202430/03/202501/04/2024
701/04/202501/04/202601/04/2025
801/04/202601/04/202701/04/2026
901/04/202701/04/202801/04/2027
1001/04/202801/04/202901/04/2028
1101/04/202901/04/193001/04/2029
12
13
142021Example Dates
15
16202514/07/2025
17202703/05/2027
18202630/11/2026
19202827/04/2028
20202331/03/2024
21202701/04/2027
22202319/08/2023
Validations
Cell Formulas
RangeFormula
B14B14=LOOKUP(2,1/($A$2:$A$4<=DATE(2021,4,3))/($B$2:$B$4>=DATE(2021,4,3)),$C$2:$C$4)
B16:B22B16=LOOKUP(2,1/($A$2:$A$10<=D16)/($B$2:$B$10>=D16),$C$2:$C$10)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,776
Messages
5,574,184
Members
412,576
Latest member
abdulla88
Top