# SUMPRODUCT with multiple values for criteria?

1. ## SUMPRODUCT with multiple values for criteria?

I'm lookign at doing a SUMPRODUCT forumula which references items, for example. =SUMPRODUCT((P1:P2000=\$A\$1)...) where \$A\$1 is a VLOOKUP'd formula to show FR1, FR2, FR3 etc.

i'd like to get an option to show "Total" which shows all from FR1 to FR20, for example, is it possible to set A1 to be a value or something which will allow that?

2. ## Re: SUMPRODUCT with multiple values for criteria?

It's not immediately obvious as to what you're doing but if I've interpreted correctly, perhaps:

=SUMPRODUCT(--ISNUMBER(SEARCH(\$A\$1,P1:P2000)))

If you set A1 such that for ALL you leave A1 blank then the above should return 2000, if A1 is not blank then it should find the appropriate number...

Note however that the use of SEARCH could lead to issues if you have non mutually-exclusive values, ie if you have FR1,FR10 etc then searching for FR1 would also count instances of FR10 (given FR1 appears within FR10)

If this is an issue you could use a slightly revised version, eg:

=SUMPRODUCT(--ISNUMBER(SEARCH(SUBSTITUTE(" "&\$A\$1&" "," ","")," "&P1:P2000&" ")))

3. ## Re: SUMPRODUCT with multiple values for criteria?

hmm, let's see. FR1 to FR10 are site offices, and I'm trying to get an aggregated sum of dollars of income that each office earns.

Does that help>

4. ## Re: SUMPRODUCT with multiple values for criteria?

I think we'd need more info to help specifically... the latter example should differentiate between the two sites whilst still allowing for criteria (A1) to be blank and thereby returning a total for all sites.

If you have only one criteria (site) then you may be able to use a SUMIF... but in short we'd need a more detailed explanation with regard to data / ranges, criteria etc...

5. ## Re: SUMPRODUCT with multiple values for criteria?

It has other criteria, which is built further into the reports but I'll give it a go tomorrow when I'm back at work and I'll see if it works thanks for your help so far!

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