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

[vantilian]

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

[Barrie Davidson]

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?

[vantilian]

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

[lenze]

Why not use an Advanced Filter to return record based on column B?

[vantilian]

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.

[Nomade]

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

[vantilian]

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

[lenze]

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.

[vantilian]

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