SUMIFS Statement

[ssmith3156]

I need a formula that is using the data from the table named "Costs". I need to sum the Hours column IF the Concatenate column has any of these values...162FOL, 392FAL, 392FGL, 392FFL, 392PFL, or 452FGL AND the date is before 6/1/2023. This is what I currently have and it is returning a result of 0 for my sample data, but it should be a sum of 3:

=SUMIFS(Costs[Hours],Costs[Concatenate],"162FOL",Costs[Concatenate],"392FAL",Costs[Concatenate],"392FGL",Costs[Concatenate],"392FFL",Costs[Concatenate],"392PPFL",Costs[Concatenate],"452FGL",Costs[Date],"<2023,6,1")

Am I using the wrong function, or does it just need a modification?

 

[Fluff]

You are doing an AND criteria, when it should be OR like

=SUM(SUMIFS(Costs[Hours],Costs[Concatenate],{"162FOL","392FAL","392FGL","392FFL","392PPFL","452FGL"},Costs[Date],"<"&DATE(2023,6,1)))

 

[Dave Patton]

consider SumProduct

Sumifs.xlsm
	G	H	I	J	K	L	M
1						1-Jun-23
2	162FO	L	5-Jun-23	1			162FO
3	382FA	O	29-May-23	2			392FA
4	452FG	L	1-May-23	3			392FG
5						3	392FF
6							392PF
7							452FG
8
6c

Cell Formulas
Range	Formula
L5	L5	=SUMPRODUCT(--(ISNUMBER(MATCH(G2:G4,M2:M7,0))),--(H2:H4="L"),--(I2:I4<L1),J2:J4)

 

[ssmith3156]

You are doing an AND criteria, when it should be OR like

=SUM(SUMIFS(Costs[Hours],Costs[Concatenate],{"162FOL","392FAL","392FGL","392FFL","392PPFL","452FGL"},Costs[Date],"<"&DATE(2023,6,1)))

This worked! Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[ssmith3156]

consider SumProduct

Thank you for the response.

 

[ssmith3156]

Glad we could help & thanks for the feedback.

You're welcome!