Sep 06 2020 04:19 AM
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
Sep 06 2020 05:09 AM
Sep 06 2020 05:19 AM
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
Sep 06 2020 06:23 AM
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?
Sep 06 2020 06:39 AM
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
Sep 06 2020 08:11 AM
SolutionSep 06 2020 08:34 AM
May 02 2023 05:43 AM
@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
May 02 2023 08:59 AM
Not sure I understood what you need. Could you please share small sample to illustrate the task?
May 03 2023 02:02 AM
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.
May 03 2023 07:27 AM
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?
May 08 2023 02:37 AM
May 08 2023 07:08 AM
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]),
""
)
Sep 06 2020 08:11 AM
SolutionIf like this
formula is
=IF(COUNTIFS($A$1:$A2,A2,$B$1:$B2,B2)>1,0,D2)