| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
I have been trying to calculate some cells based on criteria located in
several other locations. vlookup range name = rangecorrection For example, trying to return a value for cell C5. However the information is in worksheet "Correction Values" and will require the formula to search "BAAA 2.0" in column Aof the above mentioned worksheet as well as "TS" in column B in order to return the value (see below for example) currently have formula: =VLOOKUP(B4,rangecorrection,3,FALSE)&VLOOKUP(C4,ra ngecorrection,3,FALSE) Column A Column B Column C BAAA 2.0 TS Row 1 .................. 130 #N/A Row 2 ................... 230 #N/A |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Formula
So you want to look at column A in sheet Correction Values and find "BAAA 2.0" and column B in sheet Correction Values and find "TS" and if both conditions are met then return the value from column C in Correction Values? Try, =SUMPRODUCT((CorrectionValues!A1:A?="BAAA 2.0")*(CorrectionValues!B1:B?="TS")*(CorrectionVal ues C1:C?)) This will sum the values in CorrectionValues!C1:C? so long as the two conditions are met in columns A & B. If there is only one occurence it will only return that one value. The size of your search ranges need to be the same (i.e. A1:A10, B1:B10, C1:C10) You could name each column range if you prefer using named ranges vs. cell references. Does that help? Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=538519 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Formula
I tried the calculation, but it gives me a #NUM! error.
"BAAA 2.0" is a factor that could change to "BCCC 2.4" and "TS" could change to "CE"... this is what I put in =SUMPRODUCT((CorrectionValues!A:A=C4)*(CorrectionV alues!B:B=D4)*('Correction Values'!C:C)) Any ideas? "SteveG" wrote: So you want to look at column A in sheet Correction Values and find "BAAA 2.0" and column B in sheet Correction Values and find "TS" and if both conditions are met then return the value from column C in Correction Values? Try, =SUMPRODUCT((CorrectionValues!A1:A?="BAAA 2.0")*(CorrectionValues!B1:B?="TS")*(CorrectionVal ues C1:C?)) This will sum the values in CorrectionValues!C1:C? so long as the two conditions are met in columns A & B. If there is only one occurence it will only return that one value. The size of your search ranges need to be the same (i.e. A1:A10, B1:B10, C1:C10) You could name each column range if you prefer using named ranges vs. cell references. Does that help? Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=538519 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Formula
You can't use the whole column in an array formula or sumproduct working as
an array formula, change the A:A to A1:A10000 or whatever and the same for B:B, that's the reason Steve put a wildcard there in his response -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Correna" wrote in message ... I tried the calculation, but it gives me a #NUM! error. "BAAA 2.0" is a factor that could change to "BCCC 2.4" and "TS" could change to "CE"... this is what I put in =SUMPRODUCT((CorrectionValues!A:A=C4)*(CorrectionV alues!B:B=D4)*('Correction Values'!C:C)) Any ideas? "SteveG" wrote: So you want to look at column A in sheet Correction Values and find "BAAA 2.0" and column B in sheet Correction Values and find "TS" and if both conditions are met then return the value from column C in Correction Values? Try, =SUMPRODUCT((CorrectionValues!A1:A?="BAAA 2.0")*(CorrectionValues!B1:B?="TS")*(CorrectionVal ues C1:C?)) This will sum the values in CorrectionValues!C1:C? so long as the two conditions are met in columns A & B. If there is only one occurence it will only return that one value. The size of your search ranges need to be the same (i.e. A1:A10, B1:B10, C1:C10) You could name each column range if you prefer using named ranges vs. cell references. Does that help? Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=538519 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Formula
The value is now returning a "0"... should be returning 25 which is in Column
C for BAAA 2.0 TS =SUMPRODUCT('Correction Values'!A2:A65536=C4)*('Correction Values'!B2:B65536=D4)*('Correction Values'!C2:C65536) "Peo Sjoblom" wrote: You can't use the whole column in an array formula or sumproduct working as an array formula, change the A:A to A1:A10000 or whatever and the same for B:B, that's the reason Steve put a wildcard there in his response -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Correna" wrote in message ... I tried the calculation, but it gives me a #NUM! error. "BAAA 2.0" is a factor that could change to "BCCC 2.4" and "TS" could change to "CE"... this is what I put in =SUMPRODUCT((CorrectionValues!A:A=C4)*(CorrectionV alues!B:B=D4)*('Correction Values'!C:C)) Any ideas? "SteveG" wrote: So you want to look at column A in sheet Correction Values and find "BAAA 2.0" and column B in sheet Correction Values and find "TS" and if both conditions are met then return the value from column C in Correction Values? Try, =SUMPRODUCT((CorrectionValues!A1:A?="BAAA 2.0")*(CorrectionValues!B1:B?="TS")*(CorrectionVal ues C1:C?)) This will sum the values in CorrectionValues!C1:C? so long as the two conditions are met in columns A & B. If there is only one occurence it will only return that one value. The size of your search ranges need to be the same (i.e. A1:A10, B1:B10, C1:C10) You could name each column range if you prefer using named ranges vs. cell references. Does that help? Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=538519 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Formula
Disregard below... I was missing a few brackets.
thanks a million for your help "Correna" wrote: The value is now returning a "0"... should be returning 25 which is in Column C for BAAA 2.0 TS =SUMPRODUCT('Correction Values'!A2:A65536=C4)*('Correction Values'!B2:B65536=D4)*('Correction Values'!C2:C65536) "Peo Sjoblom" wrote: You can't use the whole column in an array formula or sumproduct working as an array formula, change the A:A to A1:A10000 or whatever and the same for B:B, that's the reason Steve put a wildcard there in his response -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Correna" wrote in message ... I tried the calculation, but it gives me a #NUM! error. "BAAA 2.0" is a factor that could change to "BCCC 2.4" and "TS" could change to "CE"... this is what I put in =SUMPRODUCT((CorrectionValues!A:A=C4)*(CorrectionV alues!B:B=D4)*('Correction Values'!C:C)) Any ideas? "SteveG" wrote: So you want to look at column A in sheet Correction Values and find "BAAA 2.0" and column B in sheet Correction Values and find "TS" and if both conditions are met then return the value from column C in Correction Values? Try, =SUMPRODUCT((CorrectionValues!A1:A?="BAAA 2.0")*(CorrectionValues!B1:B?="TS")*(CorrectionVal ues C1:C?)) This will sum the values in CorrectionValues!C1:C? so long as the two conditions are met in columns A & B. If there is only one occurence it will only return that one value. The size of your search ranges need to be the same (i.e. A1:A10, B1:B10, C1:C10) You could name each column range if you prefer using named ranges vs. cell references. Does that help? Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=538519 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Formula
Correna, Glad you got it with some help from Peo. I went offline yesterday so sorry for the late reply. Regards, Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=538519 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel displaying formulae as constant and not calculating formula | Excel Worksheet Functions | |||
| Match then lookup | Excel Worksheet Functions | |||
| How can i get an If formula in excel to edit another cell? | Excel Worksheet Functions | |||
| converting formula from lotus.123 to excel | Excel Worksheet Functions | |||
| Formula Integrity Not Preserved During Sort in Excel 2000 | Excel Discussion (Misc queries) | |||
Linear Mode
