nested sumif
Posted by pat on March 28, 2001 1:43 AM
id rate hours
nick, base 4
pat, away 5
nick, base 4
pat away 6
I have to sort the id,s by rate, then sum the hours for each id by each rate, this has to be done for 4000 employees
Posted by Aladin Akyurek on March 28, 2001 5:55 AM
Pat
You would use PivotTables here.
Otherwise, you have to make use of an array formula. First, all your distinct emloyees on a separate sheet in column A from A2 on.
Type "base" in B1, "away" in C1.
On the sheet with your data, select all id-values and name this range EMPLOYEES, the range with rate-values RATES, and the range with hours HOURS.
Back to the first sheet where the ids of the distinct employees are listed.
Array-enter (that is, hit CONTROL+SHIFT+ENTER to enter) the followin formula in B2:
=SUM((EMPLOYEES=$A2)*(RATES=B1)*HOURS)
Copy this to C1, then copy down across for all employees.
Aladin
Posted by mseyf on March 28, 2001 6:01 AM
Pat-
This doesn't use SumIf, but may yield the results you seek.
sort the range by ID and Rate.
insert a column between rate & hours and concatenate the ID and Rate columms with dash inbetween
(i.e. =A2&" - "&B2
make sure each column has a heading, like ID, Rate, IDRate, Hours.
Select the entire range and from the menu bar select Data>Subtotals
in the Subtotal window:
At each change in: = IDRate
Use Function: = Sum
Add subtotal to: = Hours
Then click OK
You will then see some small numbers (1,2, and 3)in the upper left corner of the spread sheet, just to the left of the column headings. If you click on the '2', this should colapse the data so only the totals show.
to copy these totals to another sheet, highlight the area you wish to copy, from the menu bar select Edit>Goto>Special>Visible cells only. Then do a Copy and paste the data into another sheet.
HTH
Mark
