How do I use the data consolidate function?

[Margaret.]

Hello,

I have one large spreadsheet that includes several scores for the one item
eg:

a 1
a 90
a 21
b 2
b 50
c 2
c 2
c 45
c 40
c 1

etc.

I would like to know how to use the data consolidate function (in simple
terms) to consolidate all the scores for a, b and c for example. So the final
score for a=112, b=52 and c=88

I understand I could go through the spreadsheet and highlight the values to
get a total for each item but the spreadsheet is quite large, approx 30,000
rows and 2.5 alphabets of columns... I am hoping that the data consolidate
function will be useful in this regard.

Thanks,

M.

 

[DILipandey]

Hi Margaret,

Assuming you have the data in column A and Column B, you can use following
formula:-

=SUMIF(A:A,"a",B:B)
=SUMIF(A:A,"b",B:B)
=SUMIF(A:A,"c",B:B)

If you have alphabets more that a,b,c then you can type them out and give
the reference instead of "a", "b", and "c" in the formula.

thanks.
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India

 

[Ron Rosenfeld]

Hello,

I have one large spreadsheet that includes several scores for the one item
eg:

a 1
a 90
a 21
b 2
b 50
c 2
c 2
c 45
c 40
c 1

etc.

I would like to know how to use the data consolidate function (in simple
terms) to consolidate all the scores for a, b and c for example. So the final
score for a=112, b=52 and c=88

I understand I could go through the spreadsheet and highlight the values to
get a total for each item but the spreadsheet is quite large, approx 30,000
rows and 2.5 alphabets of columns... I am hoping that the data consolidate
function will be useful in this regard.

Thanks,

M.

Take a look at Pivot Tables.

For your simple presentation, I labeled your two columns "Items" and "Scores"

I dragged items to "Rows" and Scores to "Values".

I got this result:

Row Labels Sum of Score
a 112
b 52
c 90
Grand Total 254
--ron

 

[MartinW]

Hi Margaret,

As Ron says a pivot table would be the best way, anyway for what it's worth,
here is another posssibility.

Just using your sample data and assuming your letters are in A1:A10
and the scores are in B1:B10.

Then make D1 = a, D2=b, D3=c
Then put this formula in E1 and drag down to E3,
=SUMIF($A$1:$A$10,D1,$B$1:$B$10)

Of course you will have to adjust things to suit your much larger range
however the same process should work no problems.

HTH
Martin