Forum Discussion
Formula SUMPRODUCT with all criteria in the same column
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 thanks for your ansuar.
M'y formula ils already complicated. I need a code to replace G6
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))- 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 criteriasanaouachani 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