Forum Discussion

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

Formulas and Functions

DexterG_III

Iron Contributor

Sep 09, 2022

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

sanaouachani
Copper Contributor
Sep 10, 2022
DexterG_III thanks for your ansuar.

M'y formula ils already complicated. I need a code to replace G6
- 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))
  - sanaouachani
    Copper Contributor
    Sep 12, 2022
    thanks again.
    
    what I'm looking for is a character to replace the blank space.
    with SUMIF formula I can use <> to replace the blank space and in this situation, the formula take into account all the criteria