User Tools

Site Tools


excel_notes

This is an old revision of the document!


tasks

sum values based on another column

=sumifs(D2:D21,F2:F21,"N",E2:E21,"Y")
<code>
will compute sum(D2:D21) where F2:F21 are N and E2:E21 are Y.

Ref:
  * https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b - talks about other ways of defining the criterion (ex:- "=A*", "<>Bananas", 32, ">32", B4, "apples", "32")

See also:
  * https://exceljet.net/formula/sum-if-cell-contains-text-in-another-cell - talks about
    * using wild cards (ex:- =sumifs(D2:D21,C2:C21,"*"&F6&"*")
    * the sumif function (ex:- =sumif(range,"*"&A1&"*",sum_range)

==== concatenate with a delimiter ====
This will combine B3, C3 and D3 with '_' as the delimiter.
<code>
=B3&"_"&C3&"_"&D3

format dollars

To put the dollar sign on the left and right justify the number, use the custom format

_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

Ref:-

To only show the dollars (but not cents)

_($* #,##0_);_($* (#,##0);_($* "-"??_);_(@_)

tags | dollar sign left justified

excel_notes.1641577015.txt.gz · Last modified: 2022/01/07 17:36 by raju