[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 155
  • Last Modified:

Sumproduct in Excel

I'm trying to use the sumproduct formula with an array that looks for a specific value in my cells instead of the entire value of the cell. I need to see if my cells have this specific value somewhere in the cell. But for some reason this isn't working. Can anyone help me figure out what I'm doing wrong?

This is the array that isn't working: --(Table1[Item Desc.]="*PDB*")

This is my entire formula

SUMPRODUCT(--(Table1[Ship Date]>Summary!Q18),--(Table1[Ship Date]<Summary!R18),--(Table1[Facility]=Summary!$U$4),--(Table1[Item Desc.]="*PDB*"),(Table1[M2M Invoice Total]))
0
Lawrence Salvucci
Asked:
Lawrence Salvucci
2 Solutions
 
Ejgil HedegaardCommented:
Replace
--(Table1[Item Desc.]="*PDB*")
with
--(ISERROR(SEARCH("PDB",Table1[Item Desc.]))=FALSE)
0
 
barry houdiniCommented:
Ejgil's suggestion should work OK but using ISNUMBER is simpler, i.e.

--ISNUMBER(SEARCH("PDB",Table1[Item Desc.]))

.....but if you have excel 2007 or later SUMIFS would be preferable - it's more efficient and it will accept the wildcards that SUMPRODUCT doesn't, i.e.

=SUMIFS(Table1[M2M Invoice Total],Table1[Ship Date],">"&Summary!Q18,Table1[Ship Date],"<"&Summary!R18,Table1[Facility],Summary!$U$4,Table1[Item Desc.],"*PDB*")

regards, barry
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thank you both for your help. I wasn't aware that you couldn't use the wildcard with SUMPRODUCT. I went with the SUMIFS solution and it works perfectly.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now