MrExcel Publishing
Your One Stop for Excel Tips & Solutions

question


Posted by Humphrey Hessel-Appiah on September 04, 2001 12:29 PM


I have data in four columns (q1, q2, q3, q4 ). Q1 starts from column B. The first column , A, contains unique accounts codes for each of the data in a row.

The data from A1:E15 is for grade one staff.
From A20:E40 is for grade 2 staff

From A45:E90 is for grade 3 staff.
:
:
From An:En is for grade k staff
The unique account codes run through all the data ranges, however they are mixed up for each of the data ranges for grade 1 staff to grade k staff.

I am supposed to add the figures for each unique account code per quarter(q)across the data ranges such that all data with the same unique account code are added together. What is the easiest way to go around this problem.



Posted by Aladin Akyurek on September 04, 2001 12:50 PM

Humphrey,

It looks like a simple SUMIF formula will do the job.

First create a list of the unique account numbers, say, in column F (by using AutoFilter, for example) from F2 on.

In G2 enter: =SUMIF($A$1:$A$1000,$F2,B$1:B$1000) [ for Q1 ]

Copy this formula first across to J2 then down for all unique accounts.


Aladin

From A20:E40 is for grade 2 staff