Help with vlookup-type formula, but vlookup won't work as fa

vantilian

New Member
Joined
Mar 3, 2002
Messages
44
I have spreadsheet data similar to below:

Spreadsheet tab name TEAMS

Col A Col B Col C
TOM RED 0
JOE RED 100
BOB WHITE 200
PAT WHITE 300
DAN BLUE 400
KEN BLUE 500

I am hoping for a formula to place in a cell that will be located within another spreadsheet tab in the same file, that will look at Column B above, and where RED or BLUE only (but not WHITE or other), will return the value in Column C. Also, if the value is zero, as in the first row above, I would like it to return a numerical 0, not #N/A if possible.

Any help will be most appreciated.

Thank you,

vantilian
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
On 2002-03-04 08:32, vantilian wrote:
I have spreadsheet data similar to below:

Spreadsheet tab name TEAMS

Col A Col B Col C
TOM RED 0
JOE RED 100
BOB WHITE 200
PAT WHITE 300
DAN BLUE 400
KEN BLUE 500

I am hoping for a formula to place in a cell that will be located within another spreadsheet tab in the same file, that will look at Column B above, and where RED or BLUE only (but not WHITE or other), will return the value in Column C. Also, if the value is zero, as in the first row above, I would like it to return a numerical 0, not #N/A if possible.

Any help will be most appreciated.

Thank you,

vantilian

Which value of BLUE would you want returned in your example above?
 
Upvote 0
I'm sorry, the more I thought about it I wasn't very clear. Actually I would want both values of BLUE. Let me express it another way. I'm hoping for a formula that will look at the TEAM spreadsheet, then for certain words in Column B (RED, BLUE) and sum their values of Column C. So, formula would return the sum of Column C value for RED, BLUE, or whatever other words were designated to sum. In this example the sum would be 1,000.

I really appreciate you taking the time to review this.

Thanks,

Russell
 
Upvote 0
I'm trying to set it up so that when the spreasheet updates the formula simply recalcs the Column C value sums for certain Names in Column B. Trying to eliminate any manual intervention following the spreadsheet update.

Thanks for asking and taking the time to review this. :)
 
Upvote 0
Actually I would want both values of BLUE. Let me express it another way. I'm hoping for a formula that will look at the TEAM spreadsheet, then for certain words in Column B (RED, BLUE) and sum their values of Column C. So, formula would return the sum of Column C value for RED, BLUE, or whatever other words were designated to sum. In this example the sum would be 1,000.

Try using an array formula with the following type syntax:
=SUM(IF(OR(B1:B4="BLUE";B1:B4="RED");C1:C4))
just remember to press Ctrl-Shift-Enter
You can also replace "Blue" and "Red" with references to cells...

Hope this works for you.

J
 
Upvote 0
J, thank you the answer. I tried using the formula you suggested, but I get the general "formula you typed contains an error". I combed over it several times to make sure I typed correctly and hit CTRL-SHIFT-ENTER for array. Again, thanks your response.

Russell
 
Upvote 0
Another option would be to use MS Query. Choose Excel Files for your Database source and direct MS query to the Worksheet where the data is located. The query can be in the same workbook or another one. Once you set up the query parameters, it can be set to run automatically.
 
Upvote 0
I've got a Summary Report spreadsheet that the TEAM (example above) spreadsheet update drives. It has other data groupings and values reported. In this case, I am trying to add one additional line to the Summary Report that would be, for example, "Color Group A" (Red & Blue) with the sum values of Column C for Red & Blue pulled via the formula in question placed on the Summary Report. Ultimate goal to have spreadsheet update drive that Summary line.

Thank you for the good suggestion, and I will consider it for other uses as well.

Russell
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top