Forum Discussion
The formula incorrectly counts the number of cells by the given parameters.
Glazyrik Unfortunately, this method will not work with 3 or more criteria arrays. Another approach would be to use the ISNUMBER / MATCH functions with the lookup_value and lookup_array parameters switched. For example:
=SUMPRODUCT((MID('bd-pr'!A1:A63;2;1)="2")* ISNUMBER(MATCH('bd-pr'!AB1:AB63;{22;24;28};0))* ISNUMBER(MATCH('bd-pr'!K1:K63;{11104;16604;22201}; 0))* ISNUMBER(MATCH('bd-pr'!AA1:AA63;{20231;20232;20233};0)))Note: the MATCH function is "type" sensitive ("22"<>22), so the quotation marks need to be excluded.
Please see the newly attached workbook...
For reference:
English (EU) Russian (RU) SUMPRODUCT СУММПРОИЗВ MID ПСТР ISNUMBER ЕЧИСЛО MATCH ПОИСКПОЗ
djclements This formula compares the 1st value in the first array in the specified range with the 1st value of the second array in the specified range and etc. I need the formula to compare any value from the first array to any value from the second array.
You can download the file from the cloud and try it. I think what I have planned is impossible to implement in excel.
https://www.dropbox.com/scl/fo/245zoqf277l0gd3bypoww/h?rlkey=oefde0uwmn00b89b2m57uhvyx&dl=0
Glazyrik Please see the attached workbook, which includes both methods side-by-side, so you can see the difference in the syntax. Does this return the desired results? I'm not exactly sure what you're trying to achieve... if it's still not what you were expecting, please manually input what the results should be, so I know what to aim for.
Kind regards.
COUNTIFS with 2D Array
djclements Yes, you understood me correctly, you did it! Thank you, but if I want to make (>2) 3 arrays, for example: =СУММПРОИЗВ(СЧЁТЕСЛИМН(
'bd-pr'!$A:$A;"?2*";
'bd-pr'!$AB:$AB;{"22";"24";"28"}; 'bd-pr'!$K:$K;{"11104":"16604":"22201"}; 'bd-pr'!$AA:$AA;{"20231":"20232":"20233"}))(it doesn't work)
Glazyrik Unfortunately, this method will not work with 3 or more criteria arrays. Another approach would be to use the ISNUMBER / MATCH functions with the lookup_value and lookup_array parameters switched. For example:
=SUMPRODUCT((MID('bd-pr'!A1:A63;2;1)="2")* ISNUMBER(MATCH('bd-pr'!AB1:AB63;{22;24;28};0))* ISNUMBER(MATCH('bd-pr'!K1:K63;{11104;16604;22201}; 0))* ISNUMBER(MATCH('bd-pr'!AA1:AA63;{20231;20232;20233};0)))Note: the MATCH function is "type" sensitive ("22"<>22), so the quotation marks need to be excluded.
Please see the newly attached workbook...
For reference:
English (EU) Russian (RU) SUMPRODUCT СУММПРОИЗВ MID ПСТР ISNUMBER ЕЧИСЛО MATCH ПОИСКПОЗ - Wow, dude, you’re amazing, thank you so much, I’m gonna study excel to be like you! ty and have a nice day^^