Forum Discussion

arumel's avatar
arumel
Copper Contributor
Sep 06, 2020
Solved

SUMIF for first values only

Hello all,    Would like some help pls ..   I have a spreadsheet setup as follows:   In column E i would like to show the budget once for each GL per month .. so in Jan i only want to see...
Formulas and Functions
arumel's avatar
arumel
Copper Contributor
Sep 06, 2020

Thanks Wyn Hopkins , but it doesn't seem to work .. maybe i am explaining it wrong ..

 

attached is my file with periods Jan -Dec - within each period i have duplicate GL account lines - in the actual column i want to show for each month the budget value for the GL only once ..

 

Thanks for your time

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 06, 2020

arumel 

Sorry, your sample confuses. You say budget for each account shall be shown once per month.

In the sample for the accounts which are not duplicates you show nothing, for repeating budget you ignore only first record and keep all the rest. Which exactly result do you expect?

  • arumel's avatar
    arumel
    Copper Contributor
    Sep 06, 2020

    Hello SergeiBaklan  .. the above pic is the result from the formula suggested by Wyn Hopkins  ..(which does not quite work out)

     

    I need column E to show only the first value of the budget for that GL (for that month) .. i.e GL 57010107 on row 12 should show 8,333 in column E ....  E14 and E15 should show zero as the GL is a duplicate for Jan .. similarly costs relating to Feb should show 8,333 in E76 and zero values for E77 and E78 ..

     

    Hope this clarifies

     

    Thanks for your time

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Sep 06, 2020

      arumel 

      If like this

      formula is

      =IF(COUNTIFS($A$1:$A2,A2,$B$1:$B2,B2)>1,0,D2)
      • Aless765's avatar
        Aless765
        Copper Contributor
        May 02, 2023

        SergeiBaklan what happens if I have the same amount on multiple rows lets say once for jan and once for feb, is this formula gonna return 0 for the one in february that I still need? how do I transpose this formula to work for text? i only need it to detele the duplicate lines next to each other, but if that same value appears a few rows below, I need it there

Resources