Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct(match range of values in 2+ cols)
Hi,
I'm trying to create a sumproduct function to look for a range of values in two different cells as follows: =SUMPRODUCT(--ISNUMBER(MATCH('Year 3 Raw Data'!$Q$2:$Q$2502,{1,4,5,6,7,8,10,11,12,14},0)),--ISNUMBER(MATCH('Year 3 Raw Data'!$S$2:$S$2502,{1,4,5,6,7,8,10,11,12,14},0)),--('Year 3 Raw Data'!$U$2:$U$2502=0),--('Year 3 Raw Data'!$W$2:$W$2502=0)) Unfortunately this is not returning the correct answer (which someone I work with was able to calculate using a statistical application). If someone could let me know where my function is problematic I would be very grateful. It appears to work if I use only one ISNUMBER(MATCH argument, not with two or more. Thank you. Kind regards, LisaM |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT or SUMIF if any values in a range equal any values in another range | Excel Worksheet Functions | |||
match identical cols | Excel Discussion (Misc queries) | |||
If function to match cols and return value of another col | Excel Discussion (Misc queries) | |||
How do I match 2 cols on two books and get price info for all matc | Excel Worksheet Functions | |||
Cond Format:re color 2 cols, skip 2 cols | Excel Worksheet Functions |