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

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.

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

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. :)

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.

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.

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

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

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

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

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?

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?

Sumproduct(match range of values in 2+ cols)
 

Hi Biff,

I sent you an email and attachment

Thank you!

LisaM

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!

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

Sumproduct(match range of values in 2+ cols)
 

Ooops. Ok. I'll try sending it again.