Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# sort, group and summing

Posted on 2011-03-18
Medium Priority
208 Views
have a spread sheet with 70000+ rows.
each row has an unique id# and a dollar figure associated with it.
Each unique id# is repeated many times.

so if rows 1-50 had unique id # 2400 and each row has a different \$ amount
and rows 51-100 had unique id # 2401 and each row has a different \$ amount
and rows 101-121 had unique id # 2402 and each row has a different \$ amount
and this pattern continues in a similar fashion for all 70000+ rows.

how can I  sum all \$ amounts for each unique id# without breaking each id# into it's own worksheet?
0
Question by:cmkeur

LVL 24

Accepted Solution

Tracy earned 2000 total points
ID: 35165970
You can do it several ways.  With Sumproduct or SumIf

=SUMPRODUCT((\$A\$1:\$A\$8=D2)*(\$B\$1:\$B\$8))

OR

=SUMIF(\$A\$1:\$A\$8,D2,\$B\$1:\$B\$8)

Have a look at the attached sample.  The IDs are in column A, the dollar amounts are in column B, the unique id's (non repeating) are in D2 and D3 and the formulas are in E2:F3.
Book1.xls
0

Author Comment

ID: 35166354
excellent thanks
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.