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

 

[Groovy Chick]

Yes I did and it didn't work.

 

[Fluff]

What is the formula in the Month column?

 

[Groovy Chick]

Yes, I did and it still didn't work.

 

[Groovy Chick]

Formula in month column is =E3 and I formatted it as mmmm so returns the month in text format.

 

[Fluff]

Then that's the problem, the cell format does not change the value of the cell, just the way it's displayed.
use

=text(e3,"mmmm")

 

[Groovy Chick]

Tried that and still not working

 

[Fluff]

In that case you need to check the rest of your data. Especially check for leading/trailing spaces.

 

[Grasor]

In your SUMIFS() formula, your 3rd criteria is "On Site" but all of the data in your example is "Off Site"

So the answer would be zero. Try changing your "Off Site" values in the table to "On Site" - does the formula work then?

 

[Groovy Chick]

Thanks very much. No, formula still not working. There was a space after off site in the validation. Also, the month needed formatting to text. However, done that and still showing value as zero. Very frustrating.

 

[Fluff]

The formula itself is fine, as can be seen in L3 & L4 below, so there is something wrong with your data.

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2				Month	Event Date		Start Date	Return to work date	Days								On/Off Site
3				January	01/01/2022		07/01/2022	09/01/2022	2			6			Blue		Off Site
4				February	01/02/2022		02/02/2022	08/02/2022	6			2			Blue		Off Site
5				January	15/01/2022		22/02/2022	28/02/2022	6						Blue		On Site
6				February	01/02/2022		03/03/2022	05/03/2022	2						Blue		On Site
7
Data

Cell Formulas
Range	Formula
L3	L3	=SUMIFS(I$3:I$52,D$3:D$52,"January",O$3:O$52,"Blue",Q$3:Q$52,"On Site")
L4	L4	=SUMIFS(I$3:I$52,D$3:D$52,"February",O$3:O$52,"Blue",Q$3:Q$52,"On Site")
D3:D6	D3	=TEXT(E3,"mmmm")
I3:I6	I3	=H3-G3