Array formula with a few conditions

[Sirishgreen]

Hi,

I am trying to find out how many "small" caravans a salesman has sold from a spreadhseet of sales. The different sizes classed as small are 10,11,12,13 and then obviously they are all initiled with the sales person who sold them, as below "JD"

[SUM(O17:O154="JD")*(G17:G154=10)+(G17:G154=11)+(G17:G154=12)+(G17:G154=13)

The above is my effort, an array formula, not working properly. Any help would be greatly appreciated.

Thanks,
Simon

[DonkeyOte]

Depending on your version of XL

Pre XL2007
=SUMPRODUCT(--(O17:O154="JD"),--(ISNUMBER(MATCH(G17:G154,{10,11,12,13},0))))
or
=SUMPRODUCT(--(O17:O154="JD"),--(G17:G154>=10),--(G17:G154<=13))

XL2007
=COUNTIFS($O$17:$O$154,"JD",$G$17:$G$154,">=10",$G$17:$G$154,"<=13")

[romperstomper]

Or:
=SUMPRODUCT((O17:O154="JD")*(G17:G154={10,11,12,13}))

[DonkeyOte]

"Or" indeed... *much* better

(assuming COUNTIFS not viable of course )

[romperstomper]

Not better, just different.
And only works with one array constant.