We can use Reference for Search Criteria field on formula using semicolon operator and exclamation mark operator. But why I can’t use it using tilde operator, so I can skipping some cells?
Hello @Naufal98,
Please, edit your question to share what do you want to do. Why do you want to skip cells? Which ones?
Just guessing:
=SUMIF($A$2:$A$11;D2;$B$2:$B$11)
Abour tilde and exclamation mark, I only can find this:
SUMIF supports the reference concatenation operator (~) only in the Criterion parameter, and only if the optional SumRange parameter is not given.
Source: Mathematical Functions.
In case of R1C1 notation, […] INDIRECT expects the exclamation mark as sheet name separator. [But] still use the dot ‘.’ sheet name separator with A1 notation.
Source: Spreadsheet Functions.
EDIT (after @erAck answer):
Reference operators.
Thank you. This solves my problem. I thought that I should use DSUM but it is too tricky for me.
You didn’t say which Search Criteria of which function, but if it’s not only one scalar value expected then (DB) Search Criteria are one cell range area. Reference lists (with the union ~ tilde operator) are usually only accepted if a function also accepts multiple arguments of cell ranges. Both the range : semicolon operator and the intersection ! exclamation mark operator create only one range of their two operands.
2 Likes