Need help with SUMIFs containing complex multiple criteria

[drizzzle_]

Hi! Please see attached image. I need to create a formula that has a lot of both limitations and inclusions for a certain project.

For this example, I need to SUM the Duration of:
+ Only the BROWN units
{From these BROWN units:}
+ Only the ones with Q1 and Q2 in the Start column
+ AND ends with anything EXCEPT Q1

Hence, the sum of the duration should only be equal to 10. Is this possible with SUM/SUMIFS or any other formula?

 

[gargamalebarbosa]

or : SUMPRODUCT((A2:A5="BROWN")*(B2:B5<>"Q3")*(C2:C5<>"Q1")*(D2:D5))

 

[Snakehips]

@drizzzle_ As you have XL365 how about using filter?
Below has the formula using hard written values eg 'Brown' but you could use cell references to establish your filter vales if you wish.

MrXL_Aug.xlsm
	A	B	C	D	E	F	G
1	Unit	Start	End	Duration		Sum	10
2	Brown	Q1	Q1	10
3	Red	Q2	Q3	10
4	Brown	Q2	Q2	10
5	Brown	Q3	Q4	10
Sheet3

Cell Formulas
Range	Formula
G1	G1	=SUM(FILTER(D2:D5,(A2:A5="Brown")*((B2:B5="Q1")+(B2:B5="Q2"))*(C2:C5<>"Q1")))

Hope that helps.