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"
The above is my effort, an array formula, not working properly. Any help would be greatly appreciated.
Please Login or Register to view this content.
Thanks,
Simon
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")
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Or:
=SUMPRODUCT((O17:O154="JD")*(G17:G154={10,11,12,13}))
Everyone who confuses correlation and causation ends up dead.
"Or" indeed... *much* better
(assuming COUNTIFS not viable of course)
Not better, just different.
And only works with one array constant.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks