SUMPRODUCT with multiple criteria in Rows and Columns (Still function as SUMPRODUCT instead of SUM or COUNT)
Results 1 to 5 of 5

Thread: SUMPRODUCT with multiple criteria in Rows and Columns (Still function as SUMPRODUCT instead of SUM or COUNT)

  1. #1
    New Member
    Join Date
    Jul 2019
    Location
    Mumbai India
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SUMPRODUCT with multiple criteria in Rows and Columns (Still function as SUMPRODUCT instead of SUM or COUNT)

    Hi,

    I have a query related to SUMPRODUCT with multiple criteria in Rows and Columns.
    Using the below example to explain the problem.






    I have data of sales figures of various products belonging to different categories for different quarters over a few years. Am trying to calculate the total Value for each category for a full year.


    2017 2017 2017 2017 2018 2018 2018 2018
    Product Category Unit value ($) Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4
    A Regular 4 57 29 42 31 80 39 38 43
    B Regular 5 29 55 50 67 26 80 70 31
    C Super 8 40 57 66 80 77 62 57 70
    D Super 9 65 51 38 48 55 36 77 79
    E Super 7 76 61 39 41 57 45 68 58
    F Regular 6 25 77 31 53 38 35 74 25

    I want to get the result without using any helper columns (adding full year numbers in helper columns).

    Required result in below format

    Category 2017 2018
    Regular
    Super


    Thanks for your help








  2. #2
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,505
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    3 Thread(s)

    Default Re: SUMPRODUCT with multiple criteria in Rows and Columns (Still function as SUMPRODUCT instead of SUM or COUNT)

    Its always useful in these instances to supply what you think the answer should be.

  3. #3
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,505
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    3 Thread(s)

    Default Re: SUMPRODUCT with multiple criteria in Rows and Columns (Still function as SUMPRODUCT instead of SUM or COUNT)

    By looking you maybe are multiplying by the unit value, even though you dont mention, so maybe:

    =SUMPRODUCT(($D$1:$K$1=B$11)*$D$3:$K$8*$C$3:$C$8*($B$3:$B$8=$A12))

    Adjust ranges to suit.

  4. #4
    Board Regular
    Join Date
    Aug 2015
    Posts
    766
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: SUMPRODUCT with multiple criteria in Rows and Columns (Still function as SUMPRODUCT instead of SUM or COUNT)

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    1
    2017
    2017
    2017
    2017
    2018
    2018
    2018
    2018
    2
    Product Category Unit value ($) Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4
    3
    A Regular
    4
    57
    29
    42
    31
    80
    39
    38
    43
    4
    B Regular
    5
    29
    55
    50
    67
    26
    80
    70
    31
    5
    C Super
    8
    40
    57
    66
    80
    77
    62
    57
    70
    6
    D Super
    9
    65
    51
    38
    48
    55
    36
    77
    79
    7
    E Super
    7
    76
    61
    39
    41
    57
    45
    68
    58
    8
    F Regular
    6
    25
    77
    31
    53
    38
    35
    74
    25
    9
    10
    Category
    2017
    2018
    11
    Regular
    2757
    2867
    12
    Super
    5281
    5947



    B11
    =IF(AND($A11<>"",B$10<>""),SUMPRODUCT(($B$3:$B$8=$A11)*($D$1:$K$1=B$10)*($D$3:$K$8)*($C$3:$C$8)),"")

    Copy across and down

  5. #5
    New Member
    Join Date
    Jul 2019
    Location
    Mumbai India
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMPRODUCT with multiple criteria in Rows and Columns (Still function as SUMPRODUCT instead of SUM or COUNT)

    Quote Originally Posted by steve the fish View Post
    By looking you maybe are multiplying by the unit value, even though you dont mention, so maybe:

    =SUMPRODUCT(($D$1:$K$1=B$11)*$D$3:$K$8*$C$3:$C$8*($B$3:$B$8=$A12))

    Adjust ranges to suit.

    Thanks a lot Steve. Worked fine

    Worked with selecting the range of cells beyond the last row, but I tried freezing the whole column as mentioned below which didn't work.

    This didn't work.
    =SUMPRODUCT(($D$1:$K$1=B$11)*$D:$K*$C:$C*($B:$B=$A12))

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •