Forum Discussion
Need help with a specific formula in Excel that won't produce needed result
I am trying to update an Excel document that was, I believe, retrieved at some point from an online resource. I have two lines tracking certain metrics, and need to add two more. However, when I type in the formulas I need for the additional metric tracking, I get only the formula I entered, and not the result. If I try to copy and edit, the original formula becomes a #VALUE! error that can only be fixed by the undo button, and I still am unable to get the formula to work further. I've attached screen shots of the main issue and the attempt to convert the result to a number. Also, I've tried making it show up as a number in formatting, but all I get is the option to keep it as the formula.
The original formula is exactly as it appears in the formula bar (and I'm only updating the first and third words). When I click the bar on either original formula, the end brackets disappear, creating the error in those cells mentioned above:
{=SUM((C13:C65=”Action Item”)*(F13:F66=”Red”))+SUM((C13:C65=” Action Item”)*(F13:F66=”High”))}
I really need this formula to work for our logging of issues. How can I fix this so it will work?
3 Replies
Hi Amy
The formula has been created as an array formula that means you need to press Ctrl+Shift+Enter after making changes rather than the normal pressing of enter.
It's difficult to advise further than that unless you can attach a copy of the file.
It may even be that a simple SUMIFS formula may serve you better than this array formula
If the logic is
How many "Action Item" in C13:C66 with "Red" or "High" in F13:F66
when i'd suggest
=SUM(COUNTIFS(C13:C66,"Action Item",F13:F66,{"Red","High"}))That helped and worked. Thank you!
