Which day of the week do we receive more number of orders without counting actual number of order in-CodePudding

Weekday     COUNT_of_weekday_in_orderdate

1   2485    #=SUMPRODUCT(--(WEEKDAY(ORDERS!C2:C16799,2)=H7)) formula 
                which I used to calculate count of days(Monday=1,Tuesday=2 
                and so on...)
2   2435
3   2332
4   2314
5   2345
6   2500
7   2387

MAX 2500
But I don't want to do this stuff and I want direct value corresponding to max value eg. here is 6 which has 2500.

Row ID Order Priority Order Date Order ID Cost Price

1 High 1/1/2010 28774 162

2 Not Specified 1/1/2010 88028 178.48

3 Critical 1/2/2010 9285 117.5

4 Low 1/2/2010 37537 1239

5 Low 1/2/2010 37537 3797.19

6 Low 1/2/2010 37537 4656.9

7 Critical 1/2/2010 44069 125.58

8 Critical 1/2/2010 44069 565.8

9 Critical 1/2/2010 89083 39.48

10 Low 1/2/2010 87946 1164.7

11 Critical 1/2/2010 90853 30.94

12 Critical 1/2/2010 90853 144.44

13 High 1/3/2010 7909 118.34

14 Low 1/3/2010 9637 26941.05

15 High 1/3/2010 9762 249.85

16 High 1/3/2010 40354 167.81

17 High 1/3/2010 40354 635.18

18 High 1/3/2010 89583 29.1

19 Low 1/3/2010 87463 120.52

CodePudding user response：

Assuming your data starts in A1 to to B7, then this will work:

=index(A1:A7,match(max(B1:B7),B1:B7,0))

max() finds the highest value, match() finds the position in column B of that value, Index() takes the daz based on the position.

CodePudding user response：

As per you given sample data you can use SORT() function if you have EXCEL-365. Try-

=@SORT(A1:B6,2,-1)

CodePudding user response：

You can get the max number of orders in a single formula if you're willing to make it more complex with a term for each weekday:

=MAX(SUMPRODUCT(--(WEEKDAY(ORDERS!C2:C16799,2)=1)),
SUMPRODUCT(--(WEEKDAY(ORDERS!C2:C16799,2)=2)),
SUMPRODUCT(--(WEEKDAY(ORDERS!C2:C16799,2)=3)),
SUMPRODUCT(--(WEEKDAY(ORDERS!C2:C16799,2)=4)),
SUMPRODUCT(--(WEEKDAY(ORDERS!C2:C16799,2)=5)),
SUMPRODUCT(--(WEEKDAY(ORDERS!C2:C16799,2)=6)),
SUMPRODUCT(--(WEEKDAY(ORDERS!C2:C16799,2)=7)))

To get the weekday, the following might work-- it does not work in my Excel 2016 because the array in the second argument isn't recognized but the array functions have been improved in newer versions, so you could try it.

=MATCH(
MAX(
SUMPRODUCT(--(WEEKDAY(ORDERS!C2:C16799,2)=1)),
SUMPRODUCT(--(WEEKDAY(ORDERS!C2:C16799,2)=2)),
SUMPRODUCT(--(WEEKDAY(ORDERS!C2:C16799,2)=3)),
SUMPRODUCT(--(WEEKDAY(ORDERS!C2:C16799,2)=4)),
SUMPRODUCT(--(WEEKDAY(ORDERS!C2:C16799,2)=5)),
SUMPRODUCT(--(WEEKDAY(ORDERS!C2:C16799,2)=6)),
SUMPRODUCT(--(WEEKDAY(ORDERS!C2:C16799,2)=7))
),
{
    SUMPRODUCT(--(WEEKDAY(ORDERS!C2:C16799,2)=1)),
    SUMPRODUCT(--(WEEKDAY(ORDERS!C2:C16799,2)=2)),
    SUMPRODUCT(--(WEEKDAY(ORDERS!C2:C16799,2)=3)),
    SUMPRODUCT(--(WEEKDAY(ORDERS!C2:C16799,2)=4)),
    SUMPRODUCT(--(WEEKDAY(ORDERS!C2:C16799,2)=5)),
    SUMPRODUCT(--(WEEKDAY(ORDERS!C2:C16799,2)=6)),
    SUMPRODUCT(--(WEEKDAY(ORDERS!C2:C16799,2)=7))
},
0)

The first part just determines the max value; the second argument of the MATCH function is an array of each of the values by weekday, the MATCH function returns the index of the matching value.