# Help with SUMPRODUCT and Match using a Range of values for Lookup

1. ## Help with SUMPRODUCT and Match using a Range of values for Lookup

Hi all,

I've been searching the internet for a few days and cannot seem to find the solution to my problem. I can usually figure out/ find on the internet solutions but I'm stumped as to why the following equation won't work: {=SUMPRODUCT((Data!\$C\$2:\$C\$13189),(--(Data!\$B\$2:\$B\$13189=\$B\$2)*(--(MATCH(Output!\$A\$2:\$A\$26,Data!\$A\$2:\$A\$13189,0)))))}

I need to be able to get the Total for the selection. The Data!\$C\$2:\$C\$13189 is the dollars I need and Data!\$B\$2:\$B\$13189=\$B\$2 is my year criteria which both work fine. When I add in the Office criteria, I get #N/A. I'm trying to allow the user to select multiple offices and I need the sumproduct to look up every office put into the Output!\$A\$2:\$A\$26 range in the Data!\$A\$2:\$A\$13189 range.

Any help will be greatly appreciated!

2. ## Re: Help with SUMPRODUCT and Match using a Range of values for Lookup

Hi,

You have a couple of issues with your formula.

Firstly, the MATCH lookup_value and lookup_array are the wrong way round.

Secondly, you need to coerce the #N/As from this MATCH into Booleans using e.g. ISNUMBER.

Try:

=SUMPRODUCT((Data!\$C\$2:\$C\$13189),(--(Data!\$B\$2:\$B\$13189=\$B\$2)*(--(ISNUMBER(MATCH(Data!\$A\$2:\$A\$13189,Output!\$A\$2:\$A\$26,0))))))

Regards

3. ## Re: Help with SUMPRODUCT and Match using a Range of values for Lookup

Oh, and by the way, not sure why you've got those curly brackets around the formula in your post as this does not need to be array-entered.

Regards

4. ## Re: Help with SUMPRODUCT and Match using a Range of values for Lookup

Thank you thank you thank you!!!!!!

I don't normally use MATCH so I could see how I got it the wrong way around. Again, thank you for your help I really appreciate it

5. ## Re: Help with SUMPRODUCT and Match using a Range of values for Lookup

You're most welcome.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1