SUMPRODUCT and EXACT problem

doq85

New Member
Joined
Jul 20, 2019
Messages
3
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.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

doq85

New Member
Joined
Jul 20, 2019
Messages
3
ListaMATIKETOPELASUMATIKETOPELASUMATIKETOPELASU
01/171920212223242526272829303112345678
Placement000000000000000000000
Placement 2200000000000000000000
Placement 3000000000000000000000
TRSo31KTVTVoLMKHPUKIK2AULAPHIVPTRiviLnimi1920212223242526272829303112345678
000000000000000010---------------------
000000000000000020---------------------
55555500500030003Person1pXRXPXQX-----------------
55555500500030004Person2öC--------------------
55555500500030005Person3öC--------------------
55555500500030006Person4öA-------------------
55555500500030007Person5HHHHHHHHHHHHHHHHHHHHH
55555500500050008Person6AA--------------------
55555500500000509Person7--------------MMMMMMM
555555005000300010Person8---------------------
005550000000300011Person9---------------------
555555005000300012Person10HHHHHHHHHHHHHHHHHHHHH

<tbody>
</tbody>

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")
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,205
Try...

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

Hope this helps!
 

Watch MrExcel Video

Forum statistics

Threads
1,102,219
Messages
5,485,454
Members
407,502
Latest member
Bloople

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top