# SUMPRODUCT and EXACT problem

#### doq85

##### New Member
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.

### 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.

#### MARZIOTULLIO

##### Well-known Member
Can you provide some sample data and what the expected outcome is?

#### doq85

##### New Member
 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

<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
Try...

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

Hope this helps!

#### doq85

##### New Member
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!

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...