Trying to edit a formula, not to sum duplicates

[AnotherUser99]

Hi everyone

I am new to this forum. Thanks for providing this service.

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 and also leave the spreadsheet attached. The formula calculates the duration of some events from another sheet that are added automatically by a PowerAutomate script that adds in 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.

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 to only calculate 1hour if the starting time is the same for that day

Instead of 15:00 in cell E17, I am expecting 12:00 after removing the duplicates from the first screenshot. Do you have any ideas on how I can modify the formula to fix it?

Find the File here

Thanks in advance for any help provided

 

[Jimmypop]

Hi there

Maybe try the below:

=IFERROR(IF(C17<>"", IF(SUMIFS(Table3[Durata], INT(Table3[Giorno Inizio]), 'Calcolo Giornaliero'!C17, Table3[Organizzatore], INDEX(Table3[Organizzatore], MATCH('Calcolo Giornaliero'!$E$2, Table3[Organizzatore], 0)))=0, "", SUMIFS(Table3[Durata], INT(Table3[Giorno Inizio]), 'Calcolo Giornaliero'!C17, Table3[Organizzatore], INDEX(Table3[Organizzatore], MATCH('Calcolo Giornaliero'!$E$2, Table3[Organizzatore], 0)))), ""), " ")

 

[AnotherUser99]

Thanks for your help Jimmypop your formula gives me:

 

[Jimmypop]

Not sure now myself... maybe someone else can give a go?

 

[Jimmypop]

Hi @AnotherUser99

I retested your original formula and when I remove the duplicates I do get 12:00 and not 15:00... How are you removing the duplicates?

 

[AnotherUser99]

Thank for trying the formula again, I know that by removing the duplicates manually it gives 12.00.

My idea was to have the formula ignore the duplicates, hy duplicates I mean the event that are on the same day at the same time.

 

[Jimmypop]

There should be a way... however I am unfortunately not getting a solution...maybe someone else has an idea...