I have 4 tabs (a tab for each quarter of 2008) with car IDs and how many
gallons of gas, $ amounts, etc. each one has used. The total amount of car
IDs changes from quarter to quarter, i.e. adding cars to the fleet and
retiring some. I am trying to total up the 4 quarters into a summary tab. I
am trying to use the vlookup function to add up all 4 quarters for each car
ID. I have to use the "false" argument because I need exact dollar amounts.
The problem is, if a car is not in the list for all 4 quarters, it returns an
error because it can't find it in some of the quarters. I hope this makes
some sense. It's a little hard to explain. Any ideas?

Hi,
Could you please post your formula, thanks

"KJ" wrote:

I have 4 tabs (a tab for each quarter of 2008) with car IDs and how many
gallons of gas, $ amounts, etc. each one has used. The total amount of car
IDs changes from quarter to quarter, i.e. adding cars to the fleet and
retiring some. I am trying to total up the 4 quarters into a summary tab. I
am trying to use the vlookup function to add up all 4 quarters for each car
ID. I have to use the "false" argument because I need exact dollar amounts.
The problem is, if a car is not in the list for all 4 quarters, it returns an
error because it can't find it in some of the quarters. I hope this makes
some sense. It's a little hard to explain. Any ideas?

I gave range names to each of the data sets Q1, Q2, ....

On the summary tab, my formula is: vlookup(unit # cell, Q1, 4- column with
the first subtotal I need,false)+vlookup(unit # cell, Q2, 4,
false)+vlookup(unit # cell, Q3, 4, false)+vlookup(unit # cell, Q4, 4, false)

Obviously if this unit # was added in quarter 3, it will return an error.
Hope this helps.

"Eduardo" wrote:

Hi,
Could you please post your formula, thanks

"KJ" wrote:

I have 4 tabs (a tab for each quarter of 2008) with car IDs and how many
gallons of gas, $ amounts, etc. each one has used. The total amount of car
IDs changes from quarter to quarter, i.e. adding cars to the fleet and
retiring some. I am trying to total up the 4 quarters into a summary tab. I
am trying to use the vlookup function to add up all 4 quarters for each car
ID. I have to use the "false" argument because I need exact dollar amounts.
The problem is, if a car is not in the list for all 4 quarters, it returns an
error because it can't find it in some of the quarters. I hope this makes
some sense. It's a little hard to explain. Any ideas?

Hi,
try

=SUMPRODUCT(--(A1=Sheet2!A1:A9),Sheet2!B1:B9)+SUMPRODUCT(--(A1=Sheet3!A1:A9),Sheet3!B1:B9)

Change the above formula to fit your needs you will have to add to more + in
order to get Q3 and Q4

To help you, A1 isn the cell where you have the names to lookup
Sheet2!A1:A9 will represent the Q1, where the names are in column A
Sheet2!B1:B9 is the column where you have the amounts to be summarized

2nd part of the formula the same steps now but Sheet3 represents Q2

"KJ" wrote:

I gave range names to each of the data sets Q1, Q2, ....

On the summary tab, my formula is: vlookup(unit # cell, Q1, 4- column with
the first subtotal I need,false)+vlookup(unit # cell, Q2, 4,
false)+vlookup(unit # cell, Q3, 4, false)+vlookup(unit # cell, Q4, 4, false)

Obviously if this unit # was added in quarter 3, it will return an error.
Hope this helps.

"Eduardo" wrote:

Hi,
Could you please post your formula, thanks

"KJ" wrote:

I have 4 tabs (a tab for each quarter of 2008) with car IDs and how many
gallons of gas, $ amounts, etc. each one has used. The total amount of car
IDs changes from quarter to quarter, i.e. adding cars to the fleet and
retiring some. I am trying to total up the 4 quarters into a summary tab. I
am trying to use the vlookup function to add up all 4 quarters for each car
ID. I have to use the "false" argument because I need exact dollar amounts.
The problem is, if a car is not in the list for all 4 quarters, it returns an
error because it can't find it in some of the quarters. I hope this makes
some sense. It's a little hard to explain. Any ideas?

oops I press post too soon

the difference with sumproduct is that will look for the value and will add
if it find it, if not it will not give you the #N/A error

"KJ" wrote:

I gave range names to each of the data sets Q1, Q2, ....

On the summary tab, my formula is: vlookup(unit # cell, Q1, 4- column with
the first subtotal I need,false)+vlookup(unit # cell, Q2, 4,
false)+vlookup(unit # cell, Q3, 4, false)+vlookup(unit # cell, Q4, 4, false)

Obviously if this unit # was added in quarter 3, it will return an error.
Hope this helps.

"Eduardo" wrote:

Hi,
Could you please post your formula, thanks

"KJ" wrote:

I have 4 tabs (a tab for each quarter of 2008) with car IDs and how many
gallons of gas, $ amounts, etc. each one has used. The total amount of car
IDs changes from quarter to quarter, i.e. adding cars to the fleet and
retiring some. I am trying to total up the 4 quarters into a summary tab. I
am trying to use the vlookup function to add up all 4 quarters for each car
ID. I have to use the "false" argument because I need exact dollar amounts.
The problem is, if a car is not in the list for all 4 quarters, it returns an
error because it can't find it in some of the quarters. I hope this makes
some sense. It's a little hard to explain. Any ideas?

Thanks! I'll give it a shot.

"Eduardo" wrote:

Hi,
try

=SUMPRODUCT(--(A1=Sheet2!A1:A9),Sheet2!B1:B9)+SUMPRODUCT(--(A1=Sheet3!A1:A9),Sheet3!B1:B9)

Change the above formula to fit your needs you will have to add to more + in
order to get Q3 and Q4

To help you, A1 isn the cell where you have the names to lookup
Sheet2!A1:A9 will represent the Q1, where the names are in column A
Sheet2!B1:B9 is the column where you have the amounts to be summarized

2nd part of the formula the same steps now but Sheet3 represents Q2

"KJ" wrote:

I gave range names to each of the data sets Q1, Q2, ....

On the summary tab, my formula is: vlookup(unit # cell, Q1, 4- column with
the first subtotal I need,false)+vlookup(unit # cell, Q2, 4,
false)+vlookup(unit # cell, Q3, 4, false)+vlookup(unit # cell, Q4, 4, false)

Obviously if this unit # was added in quarter 3, it will return an error.
Hope this helps.

"Eduardo" wrote:

Hi,
Could you please post your formula, thanks

"KJ" wrote:

I have 4 tabs (a tab for each quarter of 2008) with car IDs and how many
gallons of gas, $ amounts, etc. each one has used. The total amount of car
IDs changes from quarter to quarter, i.e. adding cars to the fleet and
retiring some. I am trying to total up the 4 quarters into a summary tab. I
am trying to use the vlookup function to add up all 4 quarters for each car
ID. I have to use the "false" argument because I need exact dollar amounts.
The problem is, if a car is not in the list for all 4 quarters, it returns an
error because it can't find it in some of the quarters. I hope this makes
some sense. It's a little hard to explain. Any ideas?

Hi KJ
I know you said you're wanting to use vlookup, but have you considered using
DataConsolidate? It works pretty well for pulling it all together if your
worksheets are set up the same-even if you have vehicles on some pages but
not on others.

Have a great weekend...
--
OneFineDay


"KJ" wrote:

I have 4 tabs (a tab for each quarter of 2008) with car IDs and how many
gallons of gas, $ amounts, etc. each one has used. The total amount of car
IDs changes from quarter to quarter, i.e. adding cars to the fleet and
retiring some. I am trying to total up the 4 quarters into a summary tab. I
am trying to use the vlookup function to add up all 4 quarters for each car
ID. I have to use the "false" argument because I need exact dollar amounts.
The problem is, if a car is not in the list for all 4 quarters, it returns an
error because it can't find it in some of the quarters. I hope this makes
some sense. It's a little hard to explain. Any ideas?