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

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

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

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

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

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

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