Sumproduct(match range of values in 2+ cols)

[LisaM]

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

 

[LisaM]

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

 

[T. Valko]

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:

Insert>Name>Define
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))

 

[LisaM]

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.

 

[LisaM]

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.

 

[T. Valko]

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.

 

[LisaM]

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]

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.

 

[LisaM]

OK.

Well, at least the function is fine. That's the main thing.

Thanks Biff for all your help.

LisaM

 

[T. Valko]

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.

 

[LisaM]

That would be great! I really appreciate that.

What's the best way to share the worksheets with you?

 

[T. Valko]

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!

 

[LisaM]

Hi Biff,

I sent you an email and attachment

Thank you!

LisaM

 

[LisaM]

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!

 

[T. Valko]

I didn't get the email?

 

[LisaM]

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