Finding a unique value in excel

-1

Lets say I have two columns, A, B, and that the A column is in order from lowest to highest.

A     |      B
1     |      1.1
2     |      1.1
2     |      1.2
3     |      4.5
3     |      9.6
4     |      1.0

I am trying to sum up all of the values in column B that pertain to a specific value in column A. How can I do this the most efficiently? I know that the INDEX function can do this, but I do not want to manually find all of the different value groups, how large they are, and what rows they are at.

user3097236

Posted 2014-01-19T00:51:09.580

Reputation: 1

1Pivot table perhaps? – Raystafarian – 2014-01-19T01:19:23.197

-1 You write "I am trying to sum up all of the values in column B that pertain to a specific value in column A" WHAT ON EARTH DOES THAT MEAN? Try to make a new column that states TRUE or FALSE, for whether or not you want to sum it. And that does mean figuring out your rule and stating it. Like if A=1,2,3 and B=3,2,1 then do you count where A=1 as a number to sum. And are you disregarding the part after the dot in column B, so 9.9 in B would correspond to 9 in A? – barlop – 2014-01-19T01:44:23.547

For instance, in column A 1 appears once, and has an associated value of 1.1, therefore the sum would be 1.1; 2 appears twice, its first value is 1.1 second is 1.2, the sum would be 2.3; 3 appears twice, first value 4.5 second value 9.6, sum is 14.1. The question is how to do this calculation dynamically. – user3097236 – 2014-01-19T01:51:07.773

@raystafarian I looked at the pivot table, and I do not see how pivot tables can classify the different numbers in different rows. I created a pivot table for the entire set; instead of it showing '1-1; 2-2.3' it shows '3-3.3'. I do not see how pivot tables can break the sum down into individual elements. – user3097236 – 2014-01-19T02:35:49.660

Answers

0

Sounds like a job for pivot table!

Highlight your table and go to Insert → pivot table and arrange it how you want. I'd do the following -

enter image description here

Raystafarian

Posted 2014-01-19T00:51:09.580

Reputation: 20 384

You might want to expand your answer to more than just a sentence. At least giving a minimal working example for the question asked would be useful. – slhck – 2014-01-19T12:19:46.093

0

You use SUMIF(range,criteria,sum_range), if you do not want to manually enter all of the possible criteria you can use in combination with a H or VLOOKUP(). This is my preference for something like this, easier to adjust and can format your output differently to a pivot table. SUMIF() Reference

leinad13

Posted 2014-01-19T00:51:09.580

Reputation: 567

Asked: 2014-01-19T00:51:09.580

Viewed: 79 times

Active: 2015-02-13T13:32:13.037