Sum True/False to return Revenue

[McNeil87]

Hello,

Am trying to create a formula that sums specific columns that return 'true' or 'false.' I'd like to return the revenue from that.

Here is what I am using, but it's not quite working:

=IF(OR[(X2,Y2,Z2)]="TRUE"),1,0*W2 (W2 is revenue).

Also tried this as a test, which worked but not sure how to return the revenue from W2:
=SUM(IF[(X2,Y2,Z2)],1,0)) -- this will return the number of 'Trues' but how can i ensure it pulls the revenue from W2?

Thanks!

 

[awoohaw]

what language version of excel are you using? I'm not familiar with the square bracket syntax.
Do you have columns of data you are doing this for?

Also, I don't think you need "TRUE" in quotes, just TRUE should work.
And I think you are missing a parenthesis after the 0.

=IF(OR[(X2,Y2,Z2)]=TRUE,1,0)*W2

 

[McNeil87]

what language version of excel are you using? I'm not familiar with the square bracket syntax.
Do you have columns of data you are doing this for?

Also, I don't think you need "TRUE" in quotes, just TRUE should work.
And I think you are missing a parenthesis after the 0.

I am using windows 10. Apologies, i had dragged my True/False columns and that created the brackets
=IF(OR(Transactions[@[Exc SPs]:[Exc RT]]="TRUE"),1,0)*$W2

 

[etaf]

maybe
=SUMIF(X1:X11, TRUE,W1:W11)+SUMIF(Y1:Y11, TRUE,W1:W11)+SUMIF(Z1:Z11, TRUE,W1:W11)

Book2
	W	X	Y	Z	AA	AB	AC
1	1
2	2						29
3	3
4	4
5	5	TRUE
6	6		TRUE
7	7
8	8	TRUE
9	9
10	10			TRUE
11	11
Sheet1

Cell Formulas
Range	Formula
AC2	AC2	=SUMIF(X1:X11, TRUE,W1:W11)+SUMIF(Y1:Y11, TRUE,W1:W11)+SUMIF(Z1:Z11, TRUE,W1:W11)

OR
=SUMPRODUCT((W1:W11)*(X1:Z11=TRUE))

Book2
	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1			1
2			2						29		29
3			3
4			4
5			5	TRUE
6			6		TRUE
7			7
8			8	TRUE
9			9
10			10			TRUE
11			11
12
Sheet1

Cell Formulas
Range	Formula
AC2	AC2	=SUMIF(X1:X11, TRUE,W1:W11)+SUMIF(Y1:Y11, TRUE,W1:W11)+SUMIF(Z1:Z11, TRUE,W1:W11)
AE2	AE2	=SUMPRODUCT((W1:W11)*(X1:Z11=TRUE))

 

[awoohaw]

try:

=IF(OR(Transactions[@[Exc SPs]:[Exc RT]])=TRUE,1,0)*$W2

 

[awoohaw]

try:

=IF(OR(Transactions[@[Exc SPs]:[Exc RT]])=TRUE,1,0)*$W2

technically, you don't even need to use the =TRUE part of the formula, since the OR function itself returns the TRUE/FALSE value needed for the IF Function.