CODEWITHSUNDEEP
excelexcel-formulasumifs
aSystemOverload
aSystemOverload

Reputation: 3074

Excel count/sumifs wildcards with numbers

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()

Upvotes: 1

Views: 1214

Answers (2)

LE7
LE7

Reputation: 1

You can turn the numbers into text, and this should solve your issue.

If you're trying to do so in a more automated context, then this subroutine should work:

Sub TextifyNumbers(Block As range)
    'This turns numbers into text so that they will work with the "*" wildcard

    x = UBound(Block.Value, 1)
    y = UBound(Block.Value, 2)

    For i = 1 To x
    For j = 1 To y

    Block(i, j).Formula = Evaluate(Chr(34) & Block(i, j).Value & Chr(34))

    Next j
    Next i
End Sub

Upvotes: 0

user4039065
user4039065

Reputation:

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.

Upvotes: 3

Related Questions