Excel: Unexpected results with SUMPRODUCT when using multiple arrays and text criteria
I'm not sure how to approach I recently switched to I'm working through a tutorial and I keep running into Hey everyone, I'm running into an issue that's driving me crazy..... I'm stuck on something that should probably be simple. I'm using Excel 365 and trying to calculate a weighted average based on multiple criteria using the SUMPRODUCT function. However, I'm getting unexpected results when my criteria include text values alongside numbers. Here’s the formula I’m using: ```excel =SUMPRODUCT((A2:A100="Category1")*(B2:B100)*(C2:C100="Yes")) / SUMPRODUCT((A2:A100="Category1")*(C2:C100="Yes")) ``` In this case, column A contains categories, column B contains the weights, and column C contains a binary text value ('Yes' or 'No'). I expect this to return a meaningful average of the weights where the category matches 'Category1' and the third column is 'Yes'. However, the result is returning `#DIV/0!`, indicating that the denominator is zero, which doesn’t seem right. I’ve checked that there are indeed matches for 'Category1' and 'Yes' in the data. To troubleshoot, I tried simplifying the formula: ```excel =SUMPRODUCT((A2:A100="Category1")*(C2:C100="Yes")) ``` This returns 0, which is unexpected given my data. I'm wondering if there’s a compatibility scenario with how Excel treats text compared to numbers in SUMPRODUCT or if there's something else I'm missing in my data setup. I also confirmed that there are no leading or trailing spaces in my text values. Any insights on why this is happening and how I can modify my formula to get the correct average would be greatly appreciated. Is there a better approach? For context: I'm using Excel on Windows 11. I'd really appreciate any guidance on this. Is this even possible? Any ideas what could be causing this? I've been using Excel for about a year now. Am I approaching this the right way? Could someone point me to the right documentation?