Struggling with SUMIFS

Ida Skou Andersen

Dear Community,

I am desperately trying to make a SUMIFS function work.

I have to columns with dates, Start date (startdato) and end date (slutdato). I need to count the days in the period between start date and end date, and add them, where the following criterias are met:

It must say "Ida Skou Andersen" in the column "Medarbejder", and it must say "Sygemelding" in the column "Type of fravær".

I have tried this, and a lot of other versions but I can't make it work. What am I doing wrong? Is it not possible to use a SUMIFS for this?

=SUMIFS(Slutdato:Slutdato - Startdato:Startdato; Medarbejder:Medarbejder; "Ida Skou Andersen"; Type af fravær:Type af fravær; "Sygemelding")

I really hope you can help me out.

Best, Ida.

Andrée Starå

Duplicate post?

-

Hi@Ida Skou Andersen

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too,[email protected])

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå| Workflow Consultant / CEO @WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community bymarking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Ida Skou Andersen

Dear@Andrée Starå,

Yes, sorry for the duplicates. It said "error" when I tried to load the question, so I tried again - and again. (It doesn't seem like I can delete the duplicates.)

Thank you so much for offering to have a quick look. :)

My sheet is not being used yet, so I would be happy to share it:

https://app.smartsheet.com/b/publish?EQBCT=9c4b49244e3b4b61bd100b867e1c610d

Thanks again for helping me out. :)

And a very nice weekend to you to. :)

Best,

Ida Skou Andersen

Andrée Starå

@Ida Skou Andersen

No worries!

Happy to help!

I've added the formula to your sheet.

Let me know that it works as expected.

Ida Skou Andersen

Dear@Andrée Starå,

Thank you so much for having a look.

I am sorry but the result I get from your fuction is not quite what I need. I apologize for not beeing clear.

What I need is a total sum of the whole sheet of the dates that meets the criteria. Not just a sum for each row.

I have tried the functions below instead, but they do not seem to work.

=SUMIF(AND(Medarbejder:Medarbejder = "Ida Skou Andersen"; Type af fravær:Type af fravær = "Sygemelding"); Slutdato:Slutdato - Startdato:Startdato)

=SUMIF(Medarbejder:Medarbejder = "Ida Skou Andersen"; AND(Type af fravær:Type af fravær = "Sygemelding"); Slutdato:Slutdato - Startdato:Startdato)

Andrée Starå

@Ida Skou Andersen

No worries!

I've updated the sheet. We'd need to use a so-called helper column to count the days and then use that in the SUMIFS next to it.

Would that work?

Ida Skou Andersen

@Andrée Starå

OK. Actually I had already made a function with a helper column, but I thought it would be great if I didn't need that extra column. I do know that I can hide the extra column - it would just be cool if it were possible to make a function without it.

But now I know there is no way around it, I will stop trying to make it work without. :)

Thank you again for all of your help.

Andrée Starå

@Ida Skou Andersen

Excellent!

You're more than welcome!

✅Remember!Did my post(s) help or answer your question or solve your problem? Please help the Community bymarking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!