Prev: #DIV/O!
Next: Data to another sheet
From: Ben on 7 Mar 2010 21:42
Hi,

I have 3 worksheets and want to combine a vlookup to find the sum of the
unique criteria. I can get it to work with 2 spreadsheets but not 3.

The formula i am using is: =IF(ISERROR(VLOOKUP($A99,'sheet1
v4'!$A$3:$AR$103,COLUMN(AR99),FALSE)),VLOOKUP($A99,'Sheet2
v4'!$A$3:$AR$98,COLUMN(AR99),FALSE),VLOOKUP($A99,'Sheet1
v4'!$A$3:$AR$103,COLUMN(AR99),FALSE))

Example:

Sheet 1
Apple 2
Banana 5
Orange 11

Sheet 2
Mango 4
Banana 5
Pear 7

Sheet 3
Apple 2
Grape 1

Summary Sheet
Apple 4
Banana 10
Orange 11
Pear 7
Grape 1

I must be missing something simple but can't figure it out, any help would
be appreciated
From: T. Valko on 7 Mar 2010 22:06
Try it like this....

=SUMIF(Sheet1!A$3:A$103,A99,Sheet1!AR$3:AR$103)+SUMIF(Sheet2!A$3:A$103,A99,Sheet2!AR$3:AR$103)+SUMIF(Sheet3!A$3:A$103,A99,Sheet3!AR$3:AR$103)

Use the appropriate sheet names.

--
Biff
Microsoft Excel MVP


"Ben" <Ben(a)discussions.microsoft.com> wrote in message
news:CDD66CA5-B2AB-4FAD-B1D2-0D1E14E4F9E5(a)microsoft.com...
> Hi,
>
> I have 3 worksheets and want to combine a vlookup to find the sum of the
> unique criteria. I can get it to work with 2 spreadsheets but not 3.
>
> The formula i am using is: =IF(ISERROR(VLOOKUP($A99,'sheet1
> v4'!$A$3:$AR$103,COLUMN(AR99),FALSE)),VLOOKUP($A99,'Sheet2
> v4'!$A$3:$AR$98,COLUMN(AR99),FALSE),VLOOKUP($A99,'Sheet1
> v4'!$A$3:$AR$103,COLUMN(AR99),FALSE))
>
> Example:
>
> Sheet 1
> Apple 2
> Banana 5
> Orange 11
>
> Sheet 2
> Mango 4
> Banana 5
> Pear 7
>
> Sheet 3
> Apple 2
> Grape 1
>
> Summary Sheet
> Apple 4
> Banana 10
> Orange 11
> Pear 7
> Grape 1
>
> I must be missing something simple but can't figure it out, any help would
> be appreciated


 | 
Pages: 1
Prev: #DIV/O!
Next: Data to another sheet