Array formula question

[Spraggs]

Good morning all.
I have an array formula shown below..
=SUMPRODUCT(--ISNUMBER(MATCH(MID($F$2:$F$1001,6,3),{"QSE","CHO"},0)),--($D$2:$D$1001="1042"),--($B$2:$B$1001>TODAY()-7))

I'm after adding to this an IF statement.
If R1 >S1,"",above array formula.
Is this possible and if so how would it be entered.

Thanks Jase

 

[Aladin Akyurek]

Good morning all.
I have an array formula shown below..
=SUMPRODUCT(--ISNUMBER(MATCH(MID($F$2:$F$1001,6,3),{"QSE","CHO"},0)),--($D$2:$D$1001="1042"),--($B$2:$B$1001>TODAY()-7))

I'm after adding to this an IF statement.
If R1 >S1,"",above array formula.
Is this possible and if so how would it be entered.

Thanks Jase

Is it not just...

=IF(R1 > S1, "", SUMPRODUCT(...))

then?

 

[Spraggs]

Thanks Aladin, my only confusion is because the sumproduct is an array formula.

 

[Aladin Akyurek]

Thanks Aladin, my only confusion is because the sumproduct is an array formula.

SumProduct is indeed a range-to-array processing function and does not need control+shift+enter. Thus, while

=IF(R1 > S1, "", SUMPRODUCT(...))

does not require control+shift+enter, the following

=IF(R1 > S1, "", 
     SUM(
        IF(ISNUMBER(MATCH(MID($F$2:$F$1001,6,3),{"QSE","CHO"},0)),
        IF($D$2:$D$1001="1042",
        IF($B$2:$B$1001>TODAY()-7,
          1)))))

would.

 

[Spraggs]

Thanks Aladin. Works perfect.