Why this formula isn't working with SUMPRODUCT

[marlina]

so i've this formula which is working
{=SUM(IF((Region=G7)+(Region=G8),Sales))}
But this formula isn't working with SUMPRODUCT. Doesn't the IF function return an array of sales based on my OR function ? SUMPRODUCT is supposed to add an array ? The SUM function is also working with CRTL+SHIFT+ENTER which shows its an array calculation.
Thanks for your time.

 

[Aladin Akyurek]

SUMPRODUCT ordinarily understands array-objects per default (so need for control+shift+enter) , but when IF or TRANSPOSE for example are invoked within a SUMPRODUCT function,
that formula must also be confirmed with control+shift+enter. That said:

{=SUM(IF((Region=G7)+(Region=G8),Sales))}

or the better extendible version...

{=SUM(IF(ISNUMBER(MATCH(Region,G7:G8,0)),Sales))}

can be rewritten as a SUMPRODUCT formula like this:

=SUMPRODUCT(--ISNUMBER(MATCH(Region,G7:G8,0)),Sales)

which needs just enter.

 

[marlina]

SUMPRODUCT ordinarily understands array-objects per default (so need for control+shift+enter) , but when IF or TRANSPOSE for example are invoked within a SUMPRODUCT function,
that formula must also be confirmed with control+shift+enter. That said:

{=SUM(IF((Region=G7)+(Region=G8),Sales))}

or the better extendible version...

{=SUM(IF(ISNUMBER(MATCH(Region,G7:G8,0)),Sales))}

can be rewritten as a SUMPRODUCT formula like this:

=SUMPRODUCT(--ISNUMBER(MATCH(Region,G7:G8,0)),Sales)

which needs just enter.

what is -- for ?

 

[Aladin Akyurek]

what is -- for ?

This converts TRUE/FALSE results that the ISNUMBER(MATCH bit necessarily returns into 1/0's.
SUMPRODUCT works only with numbers (i.e. it does not understand TRUE/FALSE values), hence that conversion.

 

[marlina]

This converts TRUE/FALSE results that the ISNUMBER(MATCH bit necessarily returns into 1/0's.
SUMPRODUCT works only with numbers (i.e. it does not understand TRUE/FALSE values), hence that conversion.

wow thank you so much.

 

[Aladin Akyurek]

wow thank you so much.

You are welcome.