lookup with repeating data in column a

[kiki_lallissee]

I'm trying to return a figure from a row using a lookup however, the data in column a may repeat. For example, one account number may appear 3 times in column a but the data in the corresponding row will be different. I am not sure which lookup feature I can use since vlookup isn't doing it...any help?

[Paul]

Are you trying to retrieve the sum of the corresponding data? A count of how many times the account number appears in the first column? The corresponding data for just the first instance of that account number?

Let us know, we'll be better able to assist.

Thanks.

[kiki_lallissee]

I'm trying to get excel to retrieve some of the information on the corresponding row every time it sees the account number in column a, which obviously, will be more than once. For example, I'd like to be able to pull the total units an account has, the issue is that there will be more than one target.

[Paul]

If you're looking to sum the number of units by Account #, based on my example below:

      A          B                C                 D      
1 | Acct#  |    Units  |          TTL-Units--By-Acct#
2 | 1000   |       13  |         Account 1000:  |  27
3 | 2000   |       10  |         Account 2000:  |  25
4 | 1000   |        5  |         Account 3000:  |   0
5 | 2000   |       11  |
6 | 1000   |        8  |
7 | 1000   |        1  |
8 | 2000   |        4  |

In D2 you would enter the formula:
=SUMPRODUCT(--($A$2:$A$8=1000),$B$2:$B$8)

For the other accounts, you could change the "=1000" part to "=2000", "=3000", etc..

If this is not what you're looking for, upload an example spreadsheet (zipped) so we can take a look. Add any detailed notes so we can understand exactly what you're looking to do and where you looking to pull data from.

[kiki_lallissee]

Oops, forgot to mention that i want to pull the data from this worksheet onto another one. I'd love to be able to pull the total units only for each account number listed on the main sheet to a cell on another spreadsheet. i don't think i can do this using subtotals...using example below, i'd like to pull the number of units totaled that account number 1000 bought onto another spreadsheet.

Account Number - Units - Name
1000 - 6 - Smith
2000 - 6 - Jones
3000 - 7 - Bennett
1000 - 6 - Smith
1000 - 5 - Smith
2000 - 4 - Jones


etc...

[Paul]

All you need to do is reference the other sheet name in your formula. For example, in sheet 2 (any cell) enter:

=SUMPRODUCT(--(Sheet1!$A$2:$A$8=1000),Sheet1!$B$2:$B$8)

Again, to reference the other 'accounts', just change the 1000 to 2000, 3000, etc.