I think this might be too complicated to explain textually, but I'll try.

The form of my data is I have a bunch of conditions (A-D in this case, but
could be many more), and there are a series of ballots associated with them
that are ranked (there are 10 here, but there could be many more). For
example:

condA 1 1 1 2 1 3 2 1 2 3
condB 2 2 2 1 2 1 1 3 3 2
condC 3 4 3 4 3 2 4 2 4 1
condD 4 3 4 3 4 4 3 4 1 4

And suppose I have another range that shows the minimum (or highest rank) of
each ballot depending on which conditions I choose to include. If all were
included, it would read simply 1 for all ballots, but if I excluded condA,
for example, it would read something like:

min 2 2 2 1 2 1 1 2 1 1

And suppose I also have a weighting factor for each condition that reflects
the weight given to all conditions that are ranked as the "next choice" for
each of the conditions (for example, the second choice of any ballot who
chose condA as first choice is worth 0.5), e.g.:

condA 0.5
condB 0.25
condC 0.1
condD 0.3

I want to be able to find how many ballots in, say, condB (though I want it
found for all conditions) ranked as the second highest choice compared to
condC when condA is eliminated (thus involving the weighting factor and the
minimum)

I think I've found that a formula might work that looks something like:

=SUMPRODUCT(((condA):(condD)=1)*((condB)=(min))*E$ 1:E$4)

where "cond_" are named references to their respective ranges ( ie: condA
would be the range of all ballots for condA), min is the range of the minimum
values of each ballot (without ballot A in this case), and E$1:E$4 refers to
the 4 cells containing the weighting factors.

I'm not sure semantically how to write all of the ranges so I don't have to
name them all individually and INDIRECT() them all .

Again, this is really hard to describe, so if this kills you, no worries.

Thanks!

"Spiky" wrote:

Somehow, I don't think our misunderstanding each other is finished. :)

But SUMPRODUCT does sound like the function you want. I think you are
describing array formulas and this function is array by default. The
usual format if you have a variable to check in A, and another
variable in B, and you want to add up numbers in C based on those is:

=SUMPRODUCT(--($A$1:$A$10=x)*($B$1:$B$10=y),$C$1:$C$10)

One thing I'm not following is how copy/paste would come up at all.
How are you avoiding that command?

Also, you seem to be saying you want to sum the results of a
sumproduct "lookup". But they are already summed, that's what
sumproduct does when entered the right way. It can also Count,
strangely enough. So, one formula may be all you really need because
of how it works on arrays. Maybe you should show us the form of your
data and then describe what to do to it.