Forum Discussion

Glazyrik's avatar
Glazyrik
Copper Contributor
Dec 08, 2023
Solved

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'...
excel
Formulas and Functions
  • djclements's avatar
    djclements
    Dec 11, 2023

    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ПОИСКПОЗ
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Dec 08, 2023

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.

Resources