Results 1 to 5 of 5

Thread: SUMPRODUCT and EXACT problem

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

    Default SUMPRODUCT and EXACT problem

    Here is the formula i am using and it is working.

    =SUMPRODUCT(--(($A$56:$A$300>=3))*(EXACT(S$56:S$300;"AE")+(EXACT(S$56:S$300;"BE")+(EXACT(S$56:S$300;"RB")))))

    I have a value that has to be >=3 in A56:A300.
    And i have two letter code in S56:S300 that might be AE, BE, qB, AB, BB, RB, PB ...... the list goes on.

    I have all the two letter codes that will appear in S56:S300 in another sheet, let's say they are in Sheet2 K2:R2.

    How can i make it get the values from Sheet2 K2:R2?

    Would make updating easier.

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

    Default Re: SUMPRODUCT and EXACT problem

    Can you provide some sample data and what the expected outcome is?

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMPRODUCT and EXACT problem

    Lista MA TI KE TO PE LA SU MA TI KE TO PE LA SU MA TI KE TO PE LA SU
    01/17 19 20 21 22 23 24 25 26 27 28 29 30 31 1 2 3 4 5 6 7 8
    Placement 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    Placement 2 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    Placement 3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
    TR So 31 KT VT Vo LM KH PU KI K2 AU LA PH IV PT Rivi Lnimi 19 20 21 22 23 24 25 26 27 28 29 30 31 1 2 3 4 5 6 7 8
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 - - - - - - - - - - - - - - - - - - - - -
    0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 0 - - - - - - - - - - - - - - - - - - - - -
    5 5 5 5 5 5 0 0 5 0 0 0 3 0 0 0 3 Person1 pX RX PX QX - - - - - - - - - - - - - - - - -
    5 5 5 5 5 5 0 0 5 0 0 0 3 0 0 0 4 Person2 öC - - - - - - - - - - - - - - - - - - - -
    5 5 5 5 5 5 0 0 5 0 0 0 3 0 0 0 5 Person3 öC - - - - - - - - - - - - - - - - - - - -
    5 5 5 5 5 5 0 0 5 0 0 0 3 0 0 0 6 Person4 öA - - - - - - - - - - - - - - - - - - -
    5 5 5 5 5 5 0 0 5 0 0 0 3 0 0 0 7 Person5 H H H H H H H H H H H H H H H H H H H H H
    5 5 5 5 5 5 0 0 5 0 0 0 5 0 0 0 8 Person6 AA - - - - - - - - - - - - - - - - - - - -
    5 5 5 5 5 5 0 0 5 0 0 0 0 0 5 0 9 Person7 - - - - - - - - - - - - - - M M M M M M M
    5 5 5 5 5 5 0 0 5 0 0 0 3 0 0 0 10 Person8 - - - - - - - - - - - - - - - - - - - - -
    0 0 5 5 5 0 0 0 0 0 0 0 3 0 0 0 11 Person9 - - - - - - - - - - - - - - - - - - - - -
    5 5 5 5 5 5 0 0 5 0 0 0 3 0 0 0 12 Person10 H H H H H H H H H H H H H H H H H H H H H

    Here is example of my table, had to cut some stuff away.

    So in Placement 2, where the value is 2, it gets the value because Person 3 and 4 has code öC and öA and the value in column C is =>3 :
    =SUMPRODUCT(--(($C$56:$C$300>=3))*(EXACT(S$56:S$300;"AC")+(EXACT(S$56:S$300;"BC")+(EXACT(S$56:S$300;"öC")))))

    So i have all the codes (AC, BC, öC) in different sheet (example Sheet2 K2:K9)
    All the different placements have their own codes.

    Could i somehow read the codes from Sheet2 K2:K9 so i dont have to manually do this (EXACT(S$56:S$300;"AC")+(EXACT(S$56:S$300;"BC")+(EXACT(S$56:S$300;"öC")

  4. #4
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: SUMPRODUCT and EXACT problem

    Try...

    =SUMPRODUCT(($A$56:$A$300>=3)*(EXACT(S$56:S$300,Sheet2!$K$2:$R$2)))

    Hope this helps!

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

    Default Re: SUMPRODUCT and EXACT problem

    Quote Originally Posted by Domenic View Post
    Try...

    =SUMPRODUCT(($A$56:$A$300>=3)*(EXACT(S$56:S$300,Sheet2!$K$2:$R$2)))

    Hope this helps!
    I can't believe i didn't try this!
    I really need to take more brakes :D

    Thanks Domenic!

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
  •