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