Forum Discussion
Not able to fix Zero-length String in Formula
Hi,
I'm using a formula that is trying to calculate the total number of items (or tickets) using COUNTIFS from a table based on 3 different criteria. However, the criteria for column W which is looking for the cell to be <> than empty is causing the total result to be wrong simply because all the empty cells from column W have a zero-length string in them.
The table is written via integration with Jira so unfortunately, how that's Jira writes the values - the cells look empty when they actually have an empty string, which totally messes up with my formulas.
I've tried to address the issue in several different ways but wasn't able to.
Please help!
I've attached a sample spreadsheet with the data and formula I'm using.
You'll notice the total from the formula is 3 when it should be 1 (the extra 2 there is because of the zero-length string issue I wasn't able to fix).
Thanks!
3 Replies
As variant with third criteria it could be used something like =COUNTIFS(W:W,"=?*") which counts all cells which have at least one character, i.e. not blanks and not empty strings.
Try ">=A" as criteria.
- As an alternative, perhaps try:
=SUMPRODUCT(--(INDIRECT("'JQL - Sprint "& A2 & "'!B:B")="Bug"),--(INDIRECT("'JQL - Sprint "& A2 & "'!U:U")=""),--(INDIRECT("'JQL - Sprint "& A2 & "'!W:W")<>""))
