Sum an array of vlookup values

[Impecunious]

Hi,

I'm working with a set of descriptive values ("Superior" or "Fair",
among others) and I've used a separate sheet to assign a number to
each of these descriptive values. These numbers can range from
positive to negative, depending upon the descriptive value in question
("Superior" corresponds to 3, while "Poor" corresponds to -3). For a
given column of these descriptive values, I'd like to show two score
totals: one total that sums up all numbers that are positive, and
another total score that sums up all numbers that are negative.

I've tried to use combinations of SUM and VLOOKUP arrays, but I can't
get this to work within a single formula combination. Please help!

Thanks.

 

[Peo Sjoblom]

=COUNTIF(A:A,"Superior")*3

=COUNTIF(A:A,"Poor")*-3

replace A:A with the range you are calculating


if you already have the numbers


=SUMIF(A:A,">0")

=SUMIF(A:A,"<0")




--


Regards,


Peo Sjoblom

 

[JW]

If I am understanding correctly, you have a column that contains
negative and positive numbers which as obtained via a VLookup
formula. You now want to get a sum of the negative numbers and a sum
of the positive numbers. Correct? If so (assuming that your neg and
pos numbers are in column B):

Sum negative numbers:
=SUMIF(B:B,"<0",B:B)

Sum positive numbers:
=SUMIF(B:B,">0",B:B)

 

[Pete_UK]

Assuming your numbers are in column F, try this:

=SUMIF(F1:F100,">0",F1:F100)

for positive numbers, and

=SUMIF(F1:F100,"<0",F1:F100)

for the negative numbers.

Hope this helps.

Pete

 

[Impecunious]

=COUNTIF(A:A,"Superior")*3

=COUNTIF(A:A,"Poor")*-3

replace A:A with the range you are calculating

if you already have the numbers

=SUMIF(A:A,">0")

=SUMIF(A:A,"<0")

--

Regards,

Peo Sjoblom









- Show quoted text -

I'm sorry, I should have been more clear. Suppose I have a Sheet1
with a column of five descriptive values:

Superior
Fair
Pretty bad
Pretty good
Superior

In another hidden sheet (Sheet2), I have a master of all possible
descriptive values and their associated score in two adjacent columns:

Superior 3
Pretty good 2
Fair 0
Pretty bad -2
Poor -3

What I'd like to do is add a total score to the bottom of the column
in Sheet1, which looks up the scores associated with these descriptive
values using the data in Sheet2. At this point, I'm not even
concerned about having separate totals for positive and negative
values. I'd settle for one score that simply combines all the points.

Thanks again.

 

[Peo Sjoblom]

You can leave out the second reference to the range

=SUMIF(B:B,"<0")

will suffice

--


Regards,


Peo Sjoblom

 

[Peo Sjoblom]

You can hard code it if you want

=SUMPRODUCT(COUNTIF(A2:A30,{"Superior";"Pretty Good";"Fair";"Pretty
Bad";"Poor"}),{3;2;0;-2;-3})


will sum the values for each descriptive word in A2:A30


if you want to use your hidden sheet


=SUMPRODUCT(COUNTIF(A2:A30,Sheet2!$A$1:$A$5),Sheet2!$B$1:$B$5)


where the descriptions are in A1:A5 and their values in B1:B5




--


Regards,


Peo Sjoblom