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