SUMIF for first values only

[arumel]

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 58,937 as the budget for GL 57010104..

Help much appreciated

[#2]

In cell E9 you could try =IF( AND( A9=A8, B9=B8), D8, 0 ) then copy that formula to the other cells in that column

[#3]

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

[#4]

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

[#5]

Hello @Sergei Baklan  .. 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

[#6 · Accepted solution]

@arumel 

If like this

formula is

=IF(COUNTIFS($A$1:$A2,A2,$B$1:$B2,B2)>1,0,D2)

[#7]

thanks so much @Sergei Baklan  .. that work.

Have a great day

[#8]

@arumel , you are welcome, glad to help

[#9]

@Sergei Baklan 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

[#10]

@Aless765 

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

[#11]

@Sergei Baklan 

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.

[#12]

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

[#13]

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

[#14]

@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]),
    ""
)