I've got a Dashboard which uses COUNTIFS and SUMIFS for the # and $ for transactions over multiple countries (columns) and that match certain criteria (rows).
Each row is split into different Fiscal Quarters and then breaks down into varies additional detail and months. The first row is 'everything', so the FQ Relative is ' * ', which works for strings in sum/countifs, but not for numerical, how can I use sum/countifs as a single formulae that can be copied down where the FQ can be zero, positive/negative numbers or a ' * ' (or something else) to represent anything.
The Dashboard is large so will need to repeat this multiple times, so it can't be a different formula (must be the same for each row).
Hopefully the below gives you a simplied view of what I'm working with.
ID | Caption | FQ Relative | FM | Other Criteria | Country A | Country B | Country C
1 | All Trans | * | * | <other criteria> | =sumif() | =sumif() | =sumif()
2 | Prev QTR | -1 | * | <other criteria> | =sumif() | =sumif() | =sumif()
3 | Current QTR | 0 | * | <other criteria> | =sumif() | =sumif() | =sumif()
4 | Next QTR | 1 | * | <other criteria> | =sumif() | =sumif() | =sumif()
Use a pair of < and > operators to provide a does not equal blank criteria for SUMIF/SUMIFS.
=sumifs(f:f, b:b, "<>")
This works against truly blank cells as well as cells that may contain a zero-length string (e.g. "") returned by a formula.