SOLVED

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

Copper Contributor

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

@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 ArrayCOUNTIFS with 2D Criteria Array

@Glazyrik 

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!

 

Hope this will help you.

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

This will help all forum participants.

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

1.png2.png3.png

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

best response confirmed by Glazyrik (Copper Contributor)
Solution

@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^^
1 best response

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

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

View solution in original post