SUF IF Array with three criteria

[thesproing]

Hi All,

Ok this is beginning to get on my nerves....

Basically i need to sum the values in a column if criteria are met in three other columns.

Two of the criteria are TEXT and one is numeric. At the moment, my best attempt is something like this:


{=SUM(IF(!$B$4:$B$2160<>"A",0)*IF($B$4:$B$2160<>"B",0)*IF(F$4:F$2160<2000,0)*J$4:J$2160)}

The last column is the one i wish to sum, the first two are text and the thried is also numeric. It is just returning a 0 at the moment, and should be much higher.

I also tried SUMPRODUCT, but it was returning a very high number, which i think was a mulpile of the two numeric arrays above....

Any ideas?

Thanks,

Olly

 

[VoG]

Try

=SUMPRODUCT(--($B$4:$B$2160<>"A"),--($B$4:$B$2160<>"B"),--(F$4:F$2160<2000),J$4:J$2160)

 

[thesproing]

Works perfectly, but what does the -- signify?

 

[VoG]

-- coerces an array of False/True to an array of 0/1 that SUMPRODUCT can operate on.

 

[Aladin Akyurek]

Hi All,

Ok this is beginning to get on my nerves....

Basically i need to sum the values in a column if criteria are met in three other columns.

Two of the criteria are TEXT and one is numeric. At the moment, my best attempt is something like this:


{=SUM(IF(!$B$4:$B$2160<>"A",0)*IF($B$4:$B$2160<>"B",0)*IF(F$4:F$2160<2000,0)*J$4:J$2160)}

The last column is the one i wish to sum, the first two are text and the thried is also numeric. It is just returning a 0 at the moment, and should be much higher.

I also tried SUMPRODUCT, but it was returning a very high number, which i think was a mulpile of the two numeric arrays above....

Any ideas?

Thanks,

Olly

Control+shift+enter, not just enter:

SUM(
  IF(1-ISNUMBER(MATCH($B$4:$B$2160,{"A","B"}0)),
  IF(ISNUMBER(F$4:F$2160),
  IF(F$4:F$2160 < 2000,
    J$4:J$2160))))