Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum an array of vlookup values
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum an array of vlookup values
=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 "Impecunious" wrote in message oups.com... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum an array of vlookup values
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) Impecunious wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum an array of vlookup values
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 On Oct 15, 5:24 pm, Impecunious wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum an array of vlookup values
On Oct 15, 11:35 am, "Peo Sjoblom" wrote:
=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 "Impecunious" wrote in message oups.com... 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.- Hide quoted text - - 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum an array of vlookup values
You can leave out the second reference to the range
=SUMIF(B:B,"<0") will suffice -- Regards, Peo Sjoblom "JW" wrote in message oups.com... 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) Impecunious wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum an array of vlookup values
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),Sheet 2!$B$1:$B$5) where the descriptions are in A1:A5 and their values in B1:B5 -- Regards, Peo Sjoblom 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup on an array of values | Excel Discussion (Misc queries) | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Array: Counting multiple values within array | Excel Worksheet Functions | |||
VLOOKUP ARRAY | Excel Discussion (Misc queries) | |||
Use array to return array of values | Excel Worksheet Functions |