Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Sum 6 separate columns of numbers that correspond to the same ID number in column A.

Posted on 2013-05-25
Medium Priority
412 Views
Column A contains postcode numbers. Column B contains suburb names where different suburb names may correspond to the same postcode number.
Columns C, E, G, I. K and M may contain vales. The objective is to sum these values where they correspond with the same postcode number in column A as per examples in Columns D, F, H, J, L and N.
Postcode-collation-example1.xlsx
0
Question by:gregfthompson
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 43

Expert Comment

ID: 39196340
Copy this in D3 and copy down and then across

=IF(COUNTIF(\$A:\$B,\$A3)=COUNTIF(\$A3:\$A3,\$A3),SUMIF(\$A:\$B,\$A3,\$C:\$E),"")
0

Author Comment

ID: 39196357
Thanks ssaqibh.

But it doesn't appear to sum where there are multiple postcodes.

Can you attach the file?

Thanks again,

Greg
0

LVL 35

Accepted Solution

[ fanpages ] earned 2000 total points
ID: 39196365
Hi,

Hmmm...

Unless I have misunderstood the requirements, I would suggest this formula in cell [D3]:

=IF(COUNTIF(A3:A\$3123,A3)=1,SUMIF(A\$3:A\$3123,A3,C\$3:C\$3123),"")

Then copy that down column [D] until you reach cell [D3123].

I have attached a workbook with suitable formulae in the other columns.

BFN,

fp.
Q-28138893.xlsx
0

Author Closing Comment

ID: 39196369
Perfect!! Thanks heaps.  Not sure what I did wrong but the file is perfect.
0

LVL 35

Expert Comment

ID: 39196372
You're very welcome.

I don't think you did anything 'wrong'

The first suggestion was not correct & did not address your requirements.
0

## Featured Post

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
###### Suggested Courses
Course of the Month6 days, 21 hours left to enroll