Forum Discussion

sanaouachani's avatar
sanaouachani
Copper Contributor
Sep 09, 2022

Formula SUMPRODUCT with all criteria in the same column

Hi,

I want to use the SUMPRODUCT formula and copy the same formula in all the table.

For some lines, there are no criteria, so I need to replace the criteria by something that means all.

I know that with SUMIF formula I can use <> to take into account all the raw. but it doesn't work with SUMPRODUCT

=+SOMMEPROD(($B$6:$B$23=F6)*($C$6:$C$23=G6)*($D$6:$D$23))

So in this example I want to replace "?TOUS LES CRITERES?" in cells G6 by a criteria that mean "all".

 

thank you for your help

 

 

thanks

Sana

 

Formulas and Functions
  • DexterG_III's avatar
    DexterG_III
    Iron Contributor
    Sep 10, 2022

    sanaouachanisanaouachani  Here's a formula that will include all records for that column if the condition cell is blank.  

     

    =SUMPRODUCT(IF(ISBLANK(F6),1,($B$6:$B$23=F6))*IF(ISBLANK(G6),1,($C$6:$C$23=G6))*($D$6:$D$23))

     

    Hope this helps,

    Dexter

      • DexterG_III's avatar
        DexterG_III
        Iron Contributor
        Sep 10, 2022

        sanaouachani try this?

         

        =SUMPRODUCT(IF(ISBLANK(F6),1,($B$6:$B$23=F6))*IF(G6="?TOUS LES CRITERES?",1,($C$6:$C$23=G6))*($D$6:$D$23))

         

Resources