# Thread: SUMPRODUCT and EXACT problem Thanks:  1 Post #5312654 (1) Likes:  1 Post #5312654 (1)

1. ## 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. ## Re: SUMPRODUCT and EXACT problem

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

3. ## 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. ## 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. ## Re: SUMPRODUCT and EXACT problem

Originally Posted by Domenic
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!