Hi everyone
I am trying to edit this formula to don't calculate duplicates, but I am not succeeding. The formula is:
=IFERROR(IF(C17<>"", IF(SUMPRODUCT((INT(Table3[Giorno Inizio])='Calcolo Giornaliero'!C17)*(Table3[Durata])*(Table3[Organizzatore]=INDEX(Table3[Organizzatore],MATCH('Calcolo Giornaliero'!$E$2,Table3[Organizzatore],0))))=0,"",SUMPRODUCT((INT(Table3[Giorno Inizio])='Calcolo Giornaliero'!C17)*(Table3[Durata])*(Table3[Organizzatore]=INDEX(Table3[Organizzatore],MATCH('Calcolo Giornaliero'!$E$2,Table3[Organizzatore],0))))),"")," ")
I will try to explain better... The formula calculates the duration of some events from another sheet that are added automatically by a PowerAutomate script that adds to the Database Master sheet events from Google Calendar. We will take as an example the 10th of March. They are duplicates because they happen on the same day at the same time, as you can see from the screenshot below.
screenshot_20230322_122852.png
The formula I have created sums the duration of the events per each day so that I get the total in the cell of another sheet, 'Calcolo Giornaliero' what I am trying to achieve is that I want the formula only to calculate 1hour if the starting time is the same for that day.
screenshot_20230322_123707.png
Instead of 15:00 in cell E17, I expect 12:00 after removing the duplicates from the first screenshot. The same should reflect in the 'Calcolo Mensile' sheet where I get the total hours per each month, but also, in there duplicates are included. Do you have any ideas on how I can modify the formula to fix it?
Thanks in advance for any help provided
Bookmarks