-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.

Raystafarian
  • 21,583
  • 11
  • 60
  • 89
user3097236
  • 1
  • 1
  • 1
    Pivot table perhaps? – Raystafarian Jan 19 '14 at 01:19
  • -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 Jan 19 '14 at 01:44
  • 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 Jan 19 '14 at 01:51
  • @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 Jan 19 '14 at 02:35

2 Answers2

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
  • 21,583
  • 11
  • 60
  • 89
  • 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 Jan 19 '14 at 12:19
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
  • 567
  • 4
  • 13