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

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 02, 2023

Aless765 

Not sure I understood what you need. Could you please share small sample to illustrate the task?

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 08, 2023

    Aless765 

    That's a different case. For the sample as in link Excel formula could be

    =IF(
    COUNTIFS(Table1[[#Headers],[key1]]:[@key1],
        [@key1]) = 1,
    SUMIFS([Forecast], [key1], [@key1]),
    ""
)

  • Aless765's avatar
    Aless765
    Copper Contributor
    May 08, 2023
    It can pick from any of the dates received, I just gave an example. To make this easier, I need the excel formula that could solve the issue below, then I can change it to fit my needs https://community.powerbi.com/t5/Desktop/Calculate-sumif-to-return-a-value-only-for-the-first-row-and/m-p/1746757#M687096
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 03, 2023

    Aless765 

    Could you please explain why in your sample 15 days for the first code? First date received is Dec 27, date solved is 26 Jan, thus we have 30 days in query. For above

    formula could be

    =IF([@status]="received", "", [@[date resolved]] - MINIFS([date received], [status], "received") )

    Or you calculate from the latest date received before date with status is solved?

  • Aless765's avatar
    Aless765
    Copper Contributor
    May 03, 2023

    SergeiBaklan 

     

    Hi,

     

    What I need is a formula in column D that will show date resolved minus date received for each individual code. The date resolved will be selected for each code based on status solved, minus the date received for the same code but on status received. I already did this by doing a sumifs minus sumifs, but when I have multiple lines for the same status it gives an error bc sumifs cannot recognize which date to pick from.

     

Resources