SOLVED

The formula incorrectly counts the number of cells by the given parameters.

Copper Contributor

The formula incorrectly counts the number of cells by the given parameters.

It doesn`t work, why? Once I specify more than one data array, the formula incorrectly counts the number of cells. Help me plz

= СУММПРОИЗВ(СЧЁТЕСЛИМН(
'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;{"*"}
))

7 Replies

Re: The formula incorrectly counts the number of cells by the given parameters.

@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

Re: The formula incorrectly counts the number of cells by the given parameters.

The formula you provided seems to be correct.

If your formula is not working as expected, let's break it down and check each part:

=СУМПРОИЗВ(СЧЁТЕСЛИМН(

'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; {"*"}

))

1. СЧЁТЕСЛИМН: This function counts the number of cells that meet multiple criteria.
2. The range references ('bd-pr'!\$A:\$A, 'bd-pr'!\$AA:\$AA, etc.) should cover the data range in the 'bd-pr' sheet.
3. Check if the criteria used in each part of the formula match the data in the 'bd-pr' sheet.
4. Confirm that the ranges specified in the criteria are appropriate and not empty.
5. If possible, evaluate each part of the formula separately to identify where the issue might be.

If the formula is still not working, you may want to provide more details about the data and criteria you are working with, or consider simplifying the formula to troubleshoot specific parts. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

Re: The formula incorrectly counts the number of cells by the given parameters.

@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.

Re: The formula incorrectly counts the number of cells by the given parameters.

@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

Re: The formula incorrectly counts the number of cells by the given parameters.

@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)

best response confirmed by Glazyrik (Copper Contributor)
Solution

Re: 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 ПОИСКПОЗ

Re: The formula incorrectly counts the number of cells by the given parameters.

Wow, dude, you’re amazing, thank you so much, I’m gonna study excel to be like you! ty and have a nice day^^
1 best response

Accepted Solutions
best response confirmed by Glazyrik (Copper Contributor)
Solution

Re: 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 ПОИСКПОЗ