SUM alternative

Craig

Care to elaborate? What do you by "DN* is treated like 10 and DSQ is treated like 11?" I'd expect 6 as result when {1,"DSQ",2,3} is SUMmed.

Aladin

=======

DNS is "Did not start" and is worth 10 points
DSQ is "Disqualified" and is worth 11 points
I need to the point counts to be SUMed, but need the text displayed in the cells.

DNS is "Did not start" and is worth 10 points
DSQ is "Disqualified" and is worth 11 points
I need to the point counts to be SUMed, but need the text displayed in the cells.

More functional than pretty, but how about a string such as this
=COUNTIF(range1,"dns")*10+COUNTIF(range1,"DSQ")*11

where range1 is your values?

good luck

More functional than pretty, but how about a string such as this
=COUNTIF(range1,"dns")*10+COUNTIF(range1,"DSQ")*11

where range1 is your values?

good luck

A solution...

{=SUM((A1:A4={"DNS","DNF","DNC","DSQ"})*{10,10,10,11},A1:A4)}

Note: This is an array formula which must be entered
using the Shift+Control+Enter key combination. The
outermost braces, {}, are not entered by you. They're
supplied by Excel in recognition of a properly
entered array formula.

Another 'non-array' solution...

with SUMPRODUCT:

=SUMPRODUCT((A1:A4={"DNS","DNF","DNC","DSQ"})*{10,10,10,11})+SUM(A1:A4)

Aladin

Note. Ian's (IML's) requires also that additional +SUM(A1:A4).

Re: Another 'non-array' solution...

Of course there's no reason to avoid an array formula,
but since the Help topic for SUMPRODUCT() implies
that it should have a minimum of 2 arguments I
would be reluctant to use SUMPRODUCT() in this fashion.

Re: Another 'non-array' solution...

I agree, unless there is a performance issue which can bias one's choice.

Adding a +0 (or *1 for that matter) makes it agree with the "Help" specs. So far I didn't notice any anomoly on that count.

Moreover, like FREQUENCY, SUMPRODUCT treats its args as "real arrays". On the other hand, there are situations for which it appears difficult to construct an alternative with these functions to the array-entered formulas.

Re: Another 'non-array' solution...

I'm not sure I follow you here, Aladin. Where would I need that? and why?

I just thought you could probably include a wild card in mine,

=COUNTIF(A1:A4,"dn?")*10+COUNTIF(A1:A4,"DSQ")*11

Thanks,
Ian

Re: Another 'non-array' solution...

> ...it agree with the "Help" specs.

The SUMPRODUCT "Help" states:

"Array1, array2, array3, ... are 2 to 30 arrays
whose components you want to multiply and then
add."

...and shows...

SUMPRODUCT(array1,array2,array3, ...)

...with "array1, array2" in bold. This convention
means that array1 and array2 are required aguments.
Since arguments are always separated by commas...

(A1:A4={"DNS","DNF","DNC","DSQ"})*{10,10,10,11})+SUM(A1:A4)

is only one argument.

Re: Another 'non-array' solution...

I thought Craig's range had also numbers along with those codes! Whence that remark about +SUM thing & why it also figures in what I proposed. BTW, wild card is a darn good idea here.

Aladin

...with correction...

Re: Another 'non-array' solution...

: I'm not sure I follow you here, Aladin. Where would I need that? and why? : I just thought you could probably include a wild card in mine, : =COUNTIF(A1:A4,"dn?")*10+COUNTIF(A1:A4,"DSQ")*11 : Thanks,

You absolutely right. The king of misreading (with a minor is typos) has struck again. My last attempt would be as you suggested:

=COUNTIF(A1:A4,"dn?")*10+COUNTIF(A1:A4,"DSQ")*11+SUM(A1:A4)

assuming he doesn't mind 10 points for dnx, etc.

Thanks again,
Ian

& an interpretation

OK, you got me "really" look at the "Help" specs. It doesn't say explicitly "at least 2 array args". Taking the "in bold" rep of args as meaning "at least 2 array args", I should come up with something different than just ponder that "+0". Mark, what follows is nothing more than a speculation or a hunch at best.

When we have

=SUMPRODUCT({1,2,1}),

what Excel might be (pretend) doing behind scenes is

=SUMPRODUCT({1,2,1},{1,1,1})

where {1,2,1} gets multplied with an "identity vector" of the same length (that is, {1,1,1}).

Any thought on this?

=================== : > ...it agree with the "Help" specs. : The SUMPRODUCT "Help" states

Re: & an interpretation

> It doesn't say explicitly "at least 2 array
> args".

Well, yeah it does...when describing the arguments
it explictly says, "...are 2 to 30 arrays". It
gives both a lower and upper bound on the number
of arguments. My concern is that while I suspect
that the SUMPRODUCT() may be complementing a
single argument with an array of 1's as you suggest,
but it's undocumented and may not be supported in
future releases. And, believe me, I tried to find
some reference to this undocumented "feature"
on Microsoft's Knowledge Base, but couldn't.

Bottom-line: I'm reluctant to encourage anyone
to rely on this "feature" when it might not be
suppoted in future releases. When confronted
Microsoft would just point to the Help topic and
say, "See!" : ( OK, you got me "really" look at the "Help" specs. It doesn't say explicitly "at least 2 array args". Taking the "in bold" rep of args as meaning "at least 2 array args", I should come up with something different than just ponder that "+0". Mark, what follows is nothing more than a speculation or a hunch at best. When we have =SUMPRODUCT({1,2,1}), what Excel might be (pretend) doing behind scenes is =SUMPRODUCT({1,2,1},{1,1,1}) where {1,2,1} gets multplied with an "identity vector" of the same length (that is, {1,1,1}). Any thought on this? ============

More on the much maligned Array Formula...

While some (and, you know who I mean) would go
out of there way to avoid an array formula. I
wouldn't. And in my continuing search for evidence
supporting the use of array formulas I give you
a passage from Frontline Systems' (the people that
created Solver) web site at http://www.frontsys.com/dotprod.htm

"An array formula in Microsoft Excel is a formula
which computes many values at once. To enter an
array formula, you select a block of cells, type
the formula, and then type CTRL + SHIFT + ENTER
to enter the single formula in all of the cells.
The formula appears enclosed in {braces}, and it
must be edited as a whole -- you cannot change
individual cells in the block. Each cell in the
block receives one valuefrom the array result.
For example, if in cells A1:A10 you enter
{=B1:B10+C1:C10}, cell A1 will contain =B1+C1,
cell A2 will contain =B2+C2, and so on. Compared
to simply copying formulas to other cells, array
formulas are faster to recalculate and take less
space; they more clearly express your intentions,
and are less subject to undetected, accidental
modifications."

Keep in mind that well-designed Solver models
use SUMPRODUCT or its proprietary "sibling",
DOTPRODUCT. DOTPRODUCT accepts multiple selections
for both arguments, and can be used in array
formulas. They actually prefer DOTPRODUCT because
it can be used in array formulas. Obviously,
these guys aren't concerned about array formulas!

Re: More on the much maligned Array Formula...

Me neither. Also looking around & experimenting. I think the evidence inconclusive on the question of whether array formulas cause interminable recals people complain about. I still think that tons of Macro's & other VBA code and, this is important, volatile functions could be main factor that invokes time-consuming recalcs. I didn't yet check, but I intend to, the "research" which you can find (thanks to Andonny) at:

http://www.DecisionModels.com/optspeedj.htm
http://www.DecisionModels.com/optspeedk.htm

Have a look at what is there & lets discuss that too.

:And in my continuing search for evidence

Bookmarked. "An array formula in Microsoft Excel is a formula

I suppose...

...there's one way of sidestepping this concern...
Overtly creating that suspected array of 1's
and using it as the 2nd argument...

=SUMPRODUCT((A1:A4={"DNS","DNF","DNC","DSQ"})*{10,10,10,11},ROW(A1:A4)*{0,0,0,0}+1)+SUM(A1:A4) > It doesn't say explicitly "at least 2 array

Bookmarked!