Sumproduct based on text values

[mountainman88]

I have been able to SUMPRODUCT a total amount in cell E4 using text entered into cells F1 and F2 to create conditions. How do I modify the formula so that if F1 contains the text "Total", that the conditions for the SUMPRODUCT are "Yes" AND "No", and if cell F2 contains the text "Total" the conditions for the SUMPRODUCT are "COS" AND "SGA" AND "Revenue"?

Thank

	A	B	C	D	E	F
1	IT?	PL	AMT		IT?	Yes/No/Total
2	Yes	COS	50000		PL	COS/SGA/Revenue/Total
3	Yes	SGA	10000
4	No	SGA	20000	Result:	=SUMPRODUCT((C2:C5),--(A2:A5=F1),--(B2:B5=F2))
5	Yes	Revenue	40000

 

[CA_Punit]

What error is it showing????

 

[Dave Patton]

What answer did you expect.
consider the following and possibly expand or use choose.

T202007c.xlsm
	A	B	C	D	E	F	G
1	IT?	PL	AMT			Total	COS
2	Yes	COS	50,000.00			Total	SGA
3	Yes	SGA	10,000.00				REVENUE
4	No	SGA	20,000.00	Result:	120,000.00
5	Yes	Revenue	40,000.00		120,000.00
3b

Cell Formulas
Range	Formula
E4	E4	=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B5,G1:G3,0))),(C2:C5))
E5	E5	=(F1="Total")*SUMPRODUCT(--(ISNUMBER(MATCH(B2:B5,G1:G3,0))),(C2:C5))

 

[Dave Patton]

T202007c.xlsm
	A	B	C	D	E	F	G
1	IT?	PL	AMT			Total	COS
2	Yes	COS	50,000.00			Total	SGA
3	Yes	SGA	10,000.00				REVENUE
4	No	SGA	20,000.00	Result:	120,000.00
5	Yes	Revenue	40,000.00		120,000.00
3b

Cell Formulas
Range	Formula
E4	E4	=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B5,G1:G3,0))),(C2:C5))
E5	E5	=(F1="Total")*SUMPRODUCT(--(ISNUMBER(MATCH(B2:B5,{"COS";"SGA";"REVENUE"},0))),(C2:C5))

 

[mountainman88]

What error is it showing????

#VALUE

thanks

 

[mountainman88]

T202007c.xlsm
	A	B	C	D	E	F	G
1	IT?	PL	AMT			Total	COS
2	Yes	COS	50,000.00			Total	SGA
3	Yes	SGA	10,000.00				REVENUE
4	No	SGA	20,000.00	Result:	120,000.00
5	Yes	Revenue	40,000.00		120,000.00
3b

Cell Formulas
Range	Formula
E4	E4	=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B5,G1:G3,0))),(C2:C5))
E5	E5	=(F1="Total")*SUMPRODUCT(--(ISNUMBER(MATCH(B2:B5,{"COS";"SGA";"REVENUE"},0))),(C2:C5))

Hey this works perfectly and doesnt even slow the sheet down too much. Thanks for teaching me.

 

[mountainman88]

T202007c.xlsm
	A	B	C	D	E	F	G
1	IT?	PL	AMT			Total	COS
2	Yes	COS	50,000.00			Total	SGA
3	Yes	SGA	10,000.00				REVENUE
4	No	SGA	20,000.00	Result:	120,000.00
5	Yes	Revenue	40,000.00		120,000.00
3b

Cell Formulas
Range	Formula
E4	E4	=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B5,G1:G3,0))),(C2:C5))
E5	E5	=(F1="Total")*SUMPRODUCT(--(ISNUMBER(MATCH(B2:B5,{"COS";"SGA";"REVENUE"},0))),(C2:C5))

I have a solution that works well, thanks for your help

 

[Dave Patton]

Thanks for the feedback.
Dave