"rolling up" numbers

[Brad Autry]

I've fairly large worksheets, around 20 to 25k rows each. 20 in total.

Some of the data contained within is as follows:

Req ID Source Interview Hired
1234 website 2 0
1234 commercial 10 2
1234 website 1 1
1234 magazine 3 0

I need to keep the data as a basic list as it is now (no combining figures
via pivot or anything), but I'd like to find a way to automate the combining
of data for redundant sources. In the example data, "website" source is
listed twice with different figures. I need a way to consolidate it to one
row, summing the figures in Interview and Hired.

Any ideas would be greatly appreciated.

Thanks in advance,
Brad

 

[Sean Timmons]

assuming your sources are limited, make a table with your source names, then
have headers for interview and hired.

=SUMIF(Sheet1!B:B,A2,Sheet1!C:C)

Assuming your sheet2 has website, would return 3 for Interview.

=SUMIF(Sheet1!B:B,A2,Sheet1!D)

Returns # hired.

If you need to include Req ID as a criterium:

=SUMPRODUCT((Sheet1!A2:A30000=A2)*(Sheet!B2:B3000=B2)*Sheet1!D)

Would reutrn sum where Req ID equals your valeu in cell A2 and Source equals
your value in B2.