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]

How about this

Book1
	A	B	C	D	E	F	G
1	IT?	PL	AMT		IT?	Total
2	Yes	COS	50000		PL	Total
3	Yes	SGA	10000
4	No	SGA	20000	Result:	120000
5	Yes	Revenue	40000
6
7
Sheet1

Cell Formulas
Range	Formula
E4	E4	=SUMPRODUCT(C2:C5,IF(F1="Total",(C2:C5)^0,--(A2:A5=F1)),IF(F2="Total",(C2:C5)^0,--(B2:B5=F2)))

 

[mountainman88]

How about this

Book1
	A	B	C	D	E	F	G
1	IT?	PL	AMT		IT?	Total
2	Yes	COS	50000		PL	Total
3	Yes	SGA	10000
4	No	SGA	20000	Result:	120000
5	Yes	Revenue	40000
6
7
Sheet1

Cell Formulas
Range	Formula
E4	E4	=SUMPRODUCT(C2:C5,IF(F1="Total",(C2:C5)^0,--(A2:A5=F1)),IF(F2="Total",(C2:C5)^0,--(B2:B5=F2)))

Did this work for you? It doesn't for me I tried something similar also before but I dont think SUMPRODUCT likes that nested IF function.

Also using the ^0 would need there to be a number in every cell in the column which may not be the case, ie some may be blank ( I should have made this clear in the example)

Thanks

 

[CA_Punit]

Book1
	A	B	C	D	E	F
1	IT?	PL	AMT		IT?	Yes
2	Yes	COS	50000		PL	SGA
3	Yes	SGA
4	No	SGA	20000	Result:	0
5	Yes	Revenue	40000
6
Sheet1

Cell Formulas
Range	Formula
E4	E4	=SUMPRODUCT(C2:C5,IF(F1="Total",((C2:C5)+1)^0,--(A2:A5=F1)),IF(F2="Total",((C2:C5)+1)^0,--(B2:B5=F2)))

Yes it is Working For me as stated in the Example. If there is Blank I have Just Added 1 to the Range so that it turns into number and then used to the power ^0 which will convert all the number into 1 if there is Total.

 

[mountainman88]

Book1
	A	B	C	D	E	F
1	IT?	PL	AMT		IT?	Yes
2	Yes	COS	50000		PL	SGA
3	Yes	SGA
4	No	SGA	20000	Result:	0
5	Yes	Revenue	40000
6
Sheet1

Cell Formulas
Range	Formula
E4	E4	=SUMPRODUCT(C2:C5,IF(F1="Total",((C2:C5)+1)^0,--(A2:A5=F1)),IF(F2="Total",((C2:C5)+1)^0,--(B2:B5=F2)))

Yes it is Working For me as stated in the Example. If there is Blank I have Just Added 1 to the Range so that it turns into number and then used to the power ^0 which will convert all the number into 1 if there is Total.

I am getting #Value when I copy your exact formula

 

[CA_Punit]

Enter with Control+Shift+Enter and Not Just Enter

 

[CA_Punit]

ng the ^0 would need there to be a number in every cell in the column which may not be the case, ie some may be blank ( I should have made this clear in the example)

For this see Post 4

=SUMPRODUCT(C2:C5,IF(F1="Total",((C2:C5)+1)^0,--(A2:A5=F1)),IF(F2="Total",((C2:C5)+1)^0,--(B2:B5=F2)))

 

[mountainman88]

Enter with Control+Shift+Enter and Not Just Enter

ah, didnt see the squiggly lines in your example,works now.

Im concerned this may be very slow with lots of array functions (the real sheet is far bigger) but i will try and see.

thanks for your help

 

[CA_Punit]

It will definetly slow down your sheet.

 

[mountainman88]

Enter with Control+Shift+Enter and Not Just Enter

Here is my more complicated formula, I am using Ctrl-Shift-enter but no luck, is it because im using offsheet ranges? or is the formula wrong?

=SUMPRODUCT(('07.21'!AF$1:AF$2000),--('07.21'!$A$1:$A$2000=$B7),--('07.21'!$BO$1:$BO$2000=$D7),IF($C$2="Total",(('07.21'!AF$1:AF$2000)+1)^0,--('07.21'!$BP$1:$BP$2000=$C$2)),IF($C$4="Total",(('07.21'!AF$1:AF$2000)+1)^0,--('07.21'!$I$1:$I$2000=$C$4)))

thanks