Forum Discussion

KuntalSingh's avatar
KuntalSingh
Helper V
2 years ago
Solved

Need Help on sumif & Countif

Hi All I have mention below data. Need to apply  formula to get the data. Concatenate Reference and ABS(Amount doc currency) in input sheet - Copy and Paste special in a new sheet. Apply sumif for...
Need Help
  • ahadkarimi's avatar
    ahadkarimi
    2 years ago

    KuntalSingh, go ahead and try it, and if you hit any snags, just reach out.

     in power query, create a new column

     

    = Table.AddColumn(YourTableName, "ConcatenatedColumn", each [A] & Number.ToText(Number.Abs([B])))

     

    SUMIF:

     

    let
    GroupedTable = Table.Group(YourTableName, {"C"}, {{"SumColumn", each List.Sum([B]), type number}}),
    MergedTable = Table.NestedJoin(YourTableName, {"C"}, GroupedTable, {"C"}, "MergedColumn", JoinKind.LeftOuter),
    ExpandedTable = Table.ExpandTableColumn(MergedTable, "MergedColumn", {"SumColumn"}, {"SumColumn"})
in
    ExpandedTable

     

    COUNTIF:

     

    let
    GroupedTable = Table.Group(YourTableName, {"C"}, {{"CountColumn", each Table.RowCount(_), Int64.Type}}),
    MergedTable = Table.NestedJoin(YourTableName, {"C"}, GroupedTable, {"C"}, "MergedColumn", JoinKind.LeftOuter),
    ExpandedTable = Table.ExpandTableColumn(MergedTable, "MergedColumn", {"CountColumn"}, {"CountColumn"})
in
    ExpandedTable

     

    COUNTIF MR8M:

     

    = Table.AddColumn(YourTableName, "CountMR8M", each if [D] = "MR8M" then 1 else 0)

     

    sum count

     

    let
    SummedTable = Table.Group(YourTableName, {}, {{"TotalMR8M", each List.Sum([CountMR8M]), type number}})
in
    SummedTable

     

    :in power query create custom column:

     

    = Table.AddColumn(YourTableName, "Condition1", each if [D] = 3 and [E] > 1 then "True" else "False")

     

    create custom column

     

    = Table.AddColumn(YourTableName, "Condition2", each if Number.Mod([B], 2) = 0 and [F] = 0 and [G] = 0 then "True" else "False")

     

     

    Did I answer your question? If so, please mark my post as the solution!
    Your Kudos are much appreciated! Proud to be a Resolver III !

jennratten's avatar
jennratten
Super User
2 years ago

Hello - can you please provide an example of the expected result so we can better understand what you are trying to accomplish?  The description provided is a bit confusing.  Thanks!

KuntalSingh's avatar
KuntalSingh
Helper V
2 years ago

Thanks for prompt reply

This is input data

ReferenceAmountindoccurr
199065-10.7
199065-10.7
NY15-0006466612.9
NY15-00064666-12.9
9125964099-15.28
912596409915.28
912596409915.28
9125964099-15.28
4130245299-20.47
413024529920.47
NY15-0006458820.5
NY15-00064588-20.5
S111229788001-149.48
S111229788001149.48
31071227-311.61
31071227-311.61
988002180482.24
988002180482.24
NY15-00059334281.2
NY15-00059334-281.2
NY15-00059334281.2
NY15-00059334-281.2
9923837950-382.63
9923837950-382.63
9930189072-127.54
9930189072-127.54
1706974-223.24
1706974-223.24
2376117343.2
2376117-343.2
10750240000220-198.33
10750240000220-198.33
I402230584-154
I402230584-154
I403081025-437.34
I403081025437.34
I403081025-437.34
9104797676-83.41
910479767683.41
INV-82351728083-109.88
INV-82351728083109.88
198141192-102.84
198141192102.84
198141192102.84
198141192-102.84
198141192-102.84
198141192102.84
AR2108052-159.38
AR2108052159.38
18720268495
187202684-95
18720268595
187202685-95
187203048-100
187203048100
187203046205
187203046-205
03S7548890-321.5
03S7548890-321.5
18720304780
187203047-80
NY15-0006455771.2
NY15-00064557-71.2
187203367-243.62
187203367243.62
NY15-0006459453.62
NY15-00064594-53.62
NY15-00064584-262.64
NY15-00064584262.64
NY15-0006467465.5
NY15-00064674-65.5
4198862563-248.2
4198862563248.2
4198862563-248.2
SI-18621-293.73
SI-18621-293.73
SI-18621293.73
198143236-102.84
198143236102.84
198143236-102.84
198143236102.84
198143239102.84
198143239-102.84
198143239-102.84
198143239102.84
NY15-00064825109.3
NY15-00064825-109.3
SVC606275-164.82
SVC606275-164.82
SVC606274-206.03
SVC606274-206.03
19814408556.02
198144085-56.02
19814408556.02
198144085-56.02
19814408656.03
198144086-56.03
198144086-56.03
19814408656.03
198144079-112.04
198144079-112.04
198144079112.04
198144079112.04
198144080-280.09
198144080-280.09
198144080280.09
198144080280.09
198144087-280.11
198144087280.11
198144087-280.11
198144087280.11
198144078480.62
198144078-480.62
198144078480.62
198144078-480.62
5019015752-124.07
5019015752-124.07
AR2113693159.38
AR2113693-159.38
H1032801247.8
H1032801-247.8
H1013701294.12
H1013701-294.12
H0994001397.08
H0994001-397.08
147455353101.65
147455353-101.65
147455492101.65
147455492-101.65
147455493101.65
187203046205

 

Output should be 

ReferenceAmountindoccurrConcatenateSumCountIFMR8MComments
000000079314-2200000000007931422000-2200031Less priority
0000000793142200000000007931422000-2200031Less priority
000000079314-2200000000007931422000-2200031Less priority
000000080007CO-7500000000080007CO7500-750010 
0000009-150000000091500-150010 
0000035-7500000035750-75010 
  • ahadkarimi's avatar
    ahadkarimi
    Solution Specialist
    2 years ago

    Hey KuntalSingh, try this and let me know if you encounter any issues.


    In a new sheet:

    =A2 & ABS(B2)

     

    =SUMIF($C$2:$C$100, C2, $B$2:$B$100)

     

    =COUNTIF($C$2:$C$100, C2)

     

    =COUNTIF(D:D, "MR8M")

     
    Home-> Conditional Formatting -> New Rule:

    =AND($D2=3, $E2>1)

     

      Home-> Conditional Formatting -> New Rule:

     

    =AND(MOD($B2, 2)=0, $F2=0, $G2=0)

     

    • KuntalSingh's avatar
      KuntalSingh
      Helper V
      2 years ago

      Thanks for relpy 

      I am able to do it in excel but need same in power query Need M code.

      • ahadkarimi's avatar
        ahadkarimi
        Solution Specialist
        2 years ago

        KuntalSingh, go ahead and try it, and if you hit any snags, just reach out.

         in power query, create a new column

         

        = Table.AddColumn(YourTableName, "ConcatenatedColumn", each [A] & Number.ToText(Number.Abs([B])))

         

        SUMIF:

         

        let
    GroupedTable = Table.Group(YourTableName, {"C"}, {{"SumColumn", each List.Sum([B]), type number}}),
    MergedTable = Table.NestedJoin(YourTableName, {"C"}, GroupedTable, {"C"}, "MergedColumn", JoinKind.LeftOuter),
    ExpandedTable = Table.ExpandTableColumn(MergedTable, "MergedColumn", {"SumColumn"}, {"SumColumn"})
in
    ExpandedTable

         

        COUNTIF:

         

        let
    GroupedTable = Table.Group(YourTableName, {"C"}, {{"CountColumn", each Table.RowCount(_), Int64.Type}}),
    MergedTable = Table.NestedJoin(YourTableName, {"C"}, GroupedTable, {"C"}, "MergedColumn", JoinKind.LeftOuter),
    ExpandedTable = Table.ExpandTableColumn(MergedTable, "MergedColumn", {"CountColumn"}, {"CountColumn"})
in
    ExpandedTable

         

        COUNTIF MR8M:

         

        = Table.AddColumn(YourTableName, "CountMR8M", each if [D] = "MR8M" then 1 else 0)

         

        sum count

         

        let
    SummedTable = Table.Group(YourTableName, {}, {{"TotalMR8M", each List.Sum([CountMR8M]), type number}})
in
    SummedTable

         

        :in power query create custom column:

         

        = Table.AddColumn(YourTableName, "Condition1", each if [D] = 3 and [E] > 1 then "True" else "False")

         

        create custom column

         

        = Table.AddColumn(YourTableName, "Condition2", each if Number.Mod([B], 2) = 0 and [F] = 0 and [G] = 0 then "True" else "False")

         

         

        Did I answer your question? If so, please mark my post as the solution!
        Your Kudos are much appreciated! Proud to be a Resolver III !