Dynamic range for a Mode formula?

[JohannesM]

Hello,

I Im trying to detirmine what article number that is most frequent in column D ,BUT with the condition that column E (week) is between 14 and 19.

Guessing that I need to implement some IF rule but don`t see how. Please save me from more fruitless hours.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=MODE(IF(E2:E100>=14,IF(E2:E100<=19,D2:D100)))

 

[JohannesM]

Thanks a bunch, that worked perfectly and I would never had figured it out by myself! And thanks for such a fast reply, I really appreciate it alot!
Can this solution also be used to give the 2:nd most used article number?
I managed it when not using weeks as condition. What I used then was: MODE(D:D) for the most frequent and MODE(IF((D:D="")+(D:D=C44),"", D:D)) for the 2:nd most frequent, were C44 was the cell containing the result of the most frequent.

 

[Aladin Akyurek]

Thanks a bunch, that worked perfectly and I would never had figured it out by myself! And thanks for such a fast reply, I really appreciate it alot!
Can this solution also be used to give the 2:nd most used article number?
[...]

Row\Col	D​	E​	F​	G​
1​				interval
2​	80​	20​		14​
3​	30​	18​		19​
4​	30​	28​		mode
5​	70​	16​		30​
6​	75​	15​		75​
7​	80​	15​
8​	50​	14​
9​	30​	17​
10​	35​	22​
11​	25​	25​
12​	75​	16​

G5, control+shift+enter, not just enter, and copy down:

=IFERROR(1/(1/MODE(IF(1-ISNUMBER(MATCH($D$2:$D$100,G$4:G4,0)),
    IF($E$2:$E$100>=G$2,IF($E$2:$E$100<=G$3,$D$2:$D$100))))),"")

Note. Referencing whole columns in array-processing formulas like the foregoing is not a good idea for reasons of efficiency.