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

Improve this question
4
  • 1
    Pivot table perhaps?
    – Raystafarian
    Jan 19, 2014 at 1: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, 2014 at 1: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, 2014 at 1: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, 2014 at 2:35

2 Answers 2

Reset to default
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

Improve this answer
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

Improve this answer
1
  • 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, 2014 at 12:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .