# SUM(IF) vs External source and partial lookup

1. ## SUM(IF) vs External source and partial lookup

Hi,

I have entered a formula as shown below; however, using an external source, it will not work when closed.
Tried to solve with SUM(IF) - but search criteria won't go throug4h.

Any suggestions on how to solve?

=SUMIFS('[ExternalSheet.xlsx]Input'!\$P:\$P;'[ExternalSheet.xlsx]Input'!\$I:\$I;"*es";'[ExternalSheet.xlsx]Input'!\$M:\$M;\$B61;'[ExternalSheet.xlsx]Input'!\$C:\$C;SameWorkbook!\$G\$1)

Thanks

2. ## Re: SUM(IF) vs External source and partial lookup

Does this work?

SUMPRODUCT((RIGHT('[ExternalSheet.xlsx]Input'!\$I:\$I,2)="es")*('[ExternalSheet.xlsx]Input'!\$M:\$M=\$B61)*('[ExternalSheet.xlsx]Input'!\$C:\$C=SameWorkbook!\$G\$1))

You'll need to incorporate RIGHT(....,2)="es"

3. ## Re: SUM(IF) vs External source and partial lookup

Thank you for your time, this works perfect!

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