Sumproduct - can in have multiple criteria in a single array?

[thorpyuk]

Hiya,

What i'm trying to do is this:

=SUMPRODUCT(--('AW08 Data'!$B$2:$B$100="D2056"),--('AW08 Data'!$A$2:$A$100=Analysis!$N$1),'AW08 Data'!C$2:C$100)

But I have another 9 codes as well as D2056.... can i use an 'or statement' within this function, or am i better off trying another route?

Thanks!

 

[Aladin Akyurek]

Hiya,

What i'm trying to do is this:

=SUMPRODUCT(--('AW08 Data'!$B$2:$B$100="D2056"),--('AW08 Data'!$A$2:$A$100=Analysis!$N$1),'AW08 Data'!C$2:C$100)

But I have another 9 codes as well as D2056.... can i use an 'or statement' within this function, or am i better off trying another route?

Thanks!

Place the relevant codes in a range like, say, X2:X8, then invoke:

=SUMPRODUCT(
   --ISNUMBER(MATCH('AW08 Data'!$B$2:$B$10,$X$2:$X$8)),
   --('AW08 Data'!$A$2:$A$100=Analysis!$N$1),
   'AW08 Data'!C$2:C$100)

 

[thorpyuk]

Thank you Aladin, that worked very well =]