Mar 08 2017
01:28 PM
- last edited on
Jul 25 2018
09:28 AM
by
TechCommunityAP
Mar 08 2017
01:28 PM
- last edited on
Jul 25 2018
09:28 AM
by
TechCommunityAP
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?
Mar 08 2017 03:56 PM
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
Mar 09 2017 12:57 AM
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"}))
Mar 09 2017 06:03 AM
That helped and worked. Thank you!