SUMPRODUCT and EXACT problem

doq85

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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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")
 
Upvote 0
Try...

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

Hope this helps!
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top