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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct(match range of values in 2+ cols)
I've just double checked this further and it doesn't work. Where it did was
due to luck or chance rather than a properly-functioning formula... "LisaM" wrote: It appears to work if I use only one ISNUMBER(MATCH argument, not with two or more. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct(match range of values in 2+ cols)
Tou're just missing some parenthesis.
This should work: =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)) Note that empty cells evaluate as 0 so those last 2 tests: $U$2:$U$2502=0 $W$2:$W$2502=0 May have to be rewritten if there are empty cells to account for. You can shorten the formula a bit by creating the defined name: InsertNameDefine Name: Array Refers to: ={1,4,5,6,7,8,10,11,12,14} Then: =SUMPRODUCT(--(ISNUMBER(MATCH('Year 3 Raw Data'!$Q$2:$Q$2502,Array,0))),--(ISNUMBER(MATCH('Year 3 Raw Data'!$S$2:$S$2502,Array,0))),--('Year 3 Raw Data'!$U$2:$U$2502=0),--('Year 3 Raw Data'!$W$2:$W$2502=0)) -- Biff Microsoft Excel MVP "LisaM" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct(match range of values in 2+ cols)
Thanks Biff.
I put the parantheses in and my values still don't match my colleague's but I'm beginning to think that maybe my function is fine but HER calculations aren't. But just to make absolutly sure, there really shouldn't be any problem with the function no matter how many ISNUMBER(MATCH I put in? In one cell I'm going to need to insert four of them. That's a great tip about naming the array, too. It cuts out a lot of typing and typo potential. Thank you very much for that suggestion. :) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct(match range of values in 2+ cols)
Sorry, me again. I double checked her answers against a table I'd created
which had analysed the data a different way (using autofilter). Her answers and my answers add up most of the time. I need to check with her about one column of calculations that she has. What I'd like the function to do is: Search Col Q for the array {1,4,5,6,7,8,10,11,12,14}, search Col S for the same array, search Col U for 0, search Col W for 0, and then tell me how many rows all of that involves. I'm not sure the function I came up with is doing this because the values aren't the same as when I used the autofilter. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct(match range of values in 2+ cols)
I'm not sure the function I came up with is
doing this because the values aren't the same as when I used the autofilter. If you're wanting to do that calculation on filtered data then you'll need a completely different formula. That formula will return the same result whether the data is filtered or not. -- Biff Microsoft Excel MVP "LisaM" wrote in message ... Sorry, me again. I double checked her answers against a table I'd created which had analysed the data a different way (using autofilter). Her answers and my answers add up most of the time. I need to check with her about one column of calculations that she has. What I'd like the function to do is: Search Col Q for the array {1,4,5,6,7,8,10,11,12,14}, search Col S for the same array, search Col U for 0, search Col W for 0, and then tell me how many rows all of that involves. I'm not sure the function I came up with is doing this because the values aren't the same as when I used the autofilter. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct(match range of values in 2+ cols)
No, I'm not trying to do the calculation on filtered data. What I'm trying to
do is double check my results by doing the calculations in two different ways - one with the function I posted and one with filters (and the autofilter is working with different columns). What I'm hoping is that both ways of calculating the data will yield the same results, but unfortunately, they're not. "T. Valko" wrote: If you're wanting to do that calculation on filtered data then you'll need a completely different formula. That formula will return the same result whether the data is filtered or not. -- Biff Microsoft Excel MVP |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct(match range of values in 2+ cols)
Ok, I see what you're trying to do.
Well, all that was wrong with the formula was the missing parenthesis. There might be data type mismatches: text numbers versus numeric numbers. Leading/trailing spaces or other unseen whitespace characters. -- Biff Microsoft Excel MVP "LisaM" wrote in message ... No, I'm not trying to do the calculation on filtered data. What I'm trying to do is double check my results by doing the calculations in two different ways - one with the function I posted and one with filters (and the autofilter is working with different columns). What I'm hoping is that both ways of calculating the data will yield the same results, but unfortunately, they're not. "T. Valko" wrote: If you're wanting to do that calculation on filtered data then you'll need a completely different formula. That formula will return the same result whether the data is filtered or not. -- Biff Microsoft Excel MVP |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct(match range of values in 2+ cols)
OK.
Well, at least the function is fine. That's the main thing. :) Thanks Biff for all your help. LisaM |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct(match range of values in 2+ cols)
Well, I'm not satisfied until we solve the problem!
If your file isn't too big, less than 1mb, and if you still can't get it to work correctly, I'll take a look at it if you'd like. -- Biff Microsoft Excel MVP "LisaM" wrote in message ... OK. Well, at least the function is fine. That's the main thing. :) Thanks Biff for all your help. LisaM |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct(match range of values in 2+ cols)
That would be great! I really appreciate that.
What's the best way to share the worksheets with you? |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct(match range of values in 2+ cols)
You can email the file. I'm at:
xl can help at comcast period net Remove "can" and change the obvious. I won't be able to get to it until tomorrow. It's almost 3:00 AM where I am and I'm getting ready to call it a day! -- Biff Microsoft Excel MVP "LisaM" wrote in message ... That would be great! I really appreciate that. What's the best way to share the worksheets with you? |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct(match range of values in 2+ cols)
Hi Biff,
I sent you an email and attachment Thank you! LisaM |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct(match range of values in 2+ cols)
I tried another function to see what that would yield and it gave me exactly
the same answer as the sumproduct function. I set up a column (AL in my spreadsheet) to count rows and placed the value 1 in each row. Cols E through H are the four terms that hold the codes I'm looking for. I gave each array a name (N_M, N and M) as you suggested in your first post. Values is the name of the spreadsheet where the columns to count are located, and this is the function I used: =SUM(IF((ISNUMBER(MATCH(Values!E3:E2503,N_M,0)))*( ISNUMBER(MATCH(Values!F3:F2503,N_M,0)))*(Values!G3 :G2503=0)*(Values!H3:H2503=0),Values!AL3:AL2503)) Unfortunately, while it returns the same values as the sumproduct function, the values do not equal those I get when I double check with autofilters. Very, very confusing! |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct(match range of values in 2+ cols)
I didn't get the email?
-- Biff Microsoft Excel MVP "LisaM" wrote in message ... Hi Biff, I sent you an email and attachment Thank you! LisaM |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct(match range of values in 2+ cols)
Ooops. Ok. I'll try sending it again.
|
Reply |
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 |