Formula SUMPRODUCT with all criteria in the same column

[sanaouachani]

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

[#2]

@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

[#3]

@DexterG_III thanks for your ansuar.

M'y formula ils already complicated. I need a code to replace G6

[#4]

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

[#5]

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

[#6]

@sanaouachani if written correctly, the conditional components of the sumproduct formula will return a True (1) when a match is found or a False (0) with no match.   The formula in my first response bypasses the conditional evaluation if cell G6 is blank and returns a True (1) regardless.  This is only 1 of 2 conditional evaluations.   For the Ventes values to be included in the sum, both conditions 1 and 2 must = True (1). 

IF(

ISBLANK(G6), <<if cell G6 is blank or empty>>

1, <<bypass or ignore the conditional evaluation and return a 1 (True) for all records>> 

($C$6:$C$23=G6) <<otherwise, look for a match in G6 - the standard conditional component in your original sumproduct formula>>

)

When I use this formula, it works according to your requirements: If a value exists in G6 or F6 (return only matching records as True) and if there is nothing in G6 or F6 (return ALL records as True).  Therefore, you should not need to replace the blank space with anything - if it is blank all records will be considered a match and be included in the sum.   

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

If this doesn't work for you after delete "?TOUS LES CRITERES?" from G6 and making it blank, please let me know the error.  

Thanks,

Dexter

[#7]

Thanks

[#8]

Thanks you, I will use sumif. It's less complicated.
Thanks you for your help