Sumifs formula not working

[Groovy Chick]

Hi, I have a spreadsheet with one tab called 2022 lost days, image below. Month starts in column D. On another tab called DaysLost I want to calculate the number of lost days per month against month, blue/white, on/off site. My formula is =SUMIFS('2022 Lost Days'!I$3:I$52,'2022 Lost Days'!D$3:D$52,”January”,'2022 Lost Days'!O$3:O$52,”Blue”,'2022 Lost Days'!Q$3:Q$52,”On Site”)
So I am using this for the below data but I am getting a zero value back but it should be 8. The month column equals the event date column so has a formula in it, the days column is return to work minus start date and blue and off site columns are data validation.
I don't understand why it isn't working. Can anyone help? Thank you.

Month

Event Date

Start Date

Return to work date

Days

On/Off Site

January

01/01/2022​

07/01/2022

09/01/2022

2

Blue

Off Site

February

01/02/2022​

02/02/2022

08/02/2022

6

Blue

Off Site

January

15/01/2022​

22/02/2022

28/02/2022

6

Blue

Off Site

February

01/02/2022​

03/03/2022

05/03/2022

2

Blue

Off Site

 

[Fluff]

You have the wrong type of quotes in the formula they should be " rather than ”

 

[Groovy Chick]

Thank you but what exactly do you mean as " rather than ". What should I be doing instead?

 

[Fluff]

If you look at the quotes in the formula you posted they are slanted rather than vertical, you need to change them to vertical quotes.

 

[Groovy Chick]

Oh, I see now!!! Sorry been looking at it for too long!!! Will try that, thank you.

 

[Fluff]

Ok,

 

[Groovy Chick]

Hi Fluff, I have tried again and used the quote above the number 2 on my keyboard. However, it seems to change them back to slanted again and still getting a zero result. Do you have any clues??? =SUMIFS('2022 Lost Time Register'!I$3:I$52,'2022 Lost Time Register'!D$3:D$52,”January”,'2022 Lost Time Register'!O$3:O$52,”Blue”,'2022 Lost Time Register'!Q$3:Q$52,”On Site”)

 

[Fluff]

Not sure why Excel is changing the quotes, unless it's something to do with the platform your on.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It should be

=SUMIFS('2022 Lost Time Register'!I$3:I$52,'2022 Lost Time Register'!D$3:D$52,"January",'2022 Lost Time Register'!O$3:O$52,"Blue",'2022 Lost Time Register'!Q$3:Q$52,"On Site")

 

[Groovy Chick]

Tried vertical quotes and still not working so I am at a loss. I've updated my account details. I'm on windows and office 2016.

 

[Fluff]

Did you copy/paste the formula from post#8 & try that?