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 ПОИСКПОЗ
Glazyrik With the COUNTIFS function (СЧЁТЕСЛИМН) you can use an array of values in up to two criteria arguments only. To do this, though, you must create a 2D array by using the row separator in one array, and the column separator in the other. The default characters for the row and column separators can vary, depending on system settings. In my system, the row separator is a semi-colon, and the column separator is a comma. However, I can see that your system is different than mine, and it's possible the column separator is a backslash. If so, try the following:
=СУММПРОИЗВ(
СЧЁТЕСЛИМН(
'bd-pr'!$A:$A; "?2???????????????-*";
'bd-pr'!$AA:$AA; "2023*";
'bd-pr'!$AB:$AB; {"22"\"24"\"28"};
'bd-pr'!$T:$T; {"1";"2";"3"};
'bd-pr'!$G:$G; "*"
)
)
If that doesn't work, you can also try inputting the criteria in a cell range, then reference those ranges in the formula. For example:
=СУММПРОИЗВ(
СЧЁТЕСЛИМН(
'bd-pr'!$A:$A; "?2???????????????-*";
'bd-pr'!$AA:$AA; "2023*";
'bd-pr'!$AB:$AB; B1:D1;
'bd-pr'!$T:$T; A2:A4;
'bd-pr'!$G:$G; "*"
)
)
COUNTIFS with 2D Criteria Array
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 ПОИСКПОЗ