Pulling multiple results from one column that correspond to a second column

[EYF]

Hi There! I have been poking around, but the best answer I can find is a vlookup and an if function, but they don't seem to be working.

Take the following:
A B C
Jim 5 2010
Jim 5 2010
Jim 6 2010
Jim 7 2013
Aaron 5 2009
Aaron 7 2009
Sara 6 2010
Sara 6 2013

How could I set up sheets of data (one for each year 2009-2013) where the cells in the first row will say a name and the combined total of column b for that year? For instance, the 2010 sheet would show:

A B
Jim 16
Sara 6

the 2009:
A B
Aaron 12

I have summed two v lookups (using true and then false) but that will only give me the first and last data point, so for instance in 2010 Jim would=11. I also have hundreds of rows of data. Please help if you can, it would be greatly appreciated!

[NBVC]

See attached.

In Sheet 1 use a couple of helper columns.

So assuming your data occupies column A:C, then in D2:

=IF(COUNTIFS(A$2:A2,A2,C$2:C2,C2)=1,A2,"")

copied down

in E2:

=IF(D2="","",C2&"_"&COUNTIFS(C$2:C2,C2,D$2:D2,"?*"))

copied down

Then assuming the years are in A1 of each sheet,

Select all sheets (select first annual sheet, hold Shift key, select last sheet)

Then enter formula in first cell for you summary:

=IFERROR(INDEX(Sheet1!A:A,MATCH($A$1&"_"&ROWS($A$2:$A2),Sheet1!$E:$E,0)),"")

copied down as far as you need to get all info

in adjacent cell, assuming you put that in A2:

=IF(A2="","",SUMIFS(Sheet1!$B:$B,Sheet1!A:A,A2,Sheet1!C:C,$A$1))

copied down.

Now you can right-click a tab and ungroup.

[oeldere]

And I used the workbook of NBVC (thank you for that) to make a pivot table.

See the example.

[EYF]

Hey NBVC,

Thanks for your suggestions, but I'm not sure those functions work. In the workbook you have attached, the info for the 2009 and 2010 sheets does not correspond to the data on the first sheet. I also tried to apply your functions to my data, but it doesn't really seem to work for hundreds of rows of data. Any suggestions on how I might change it? Thanks so much for all of your help!

[NBVC]

In A1 of the 2009 sheet, just change 2010 to 2009...

If still you have an issue, attach a sample of your own that I can apply formulas, etc to.

[EYF]

It was my mistake-the names weren't in order and so the functions weren't working. Thanks so much for your help!

[NBVC]

Ok Great.. and you're welcome

Can you please remember to mark your thread as SOLVED?

Thanks.