SUMPRODUCT condition of an array

[zsnemeth]

Dear Excel-Pro Community,

I need Your help.

I am looking for a solution, where I can do the following:
I have a table, where I want to build a SUMPRODUCT formula, with multiple conditions.
I am already OK with the conditions, but in the table I do have a column (Named: Weighting), where I have % values.

On my sheet I do have a checkbox, and if it is checked, the connected cell (name this: ChB_Weighting) shows TRUE, if unchecked, of course FALSE.

I would like to have a formula, which can understand this, and if this cell shows TRUE, then my formula will be something like this:
=SUMPRODUCT(MyValues*(CriteriaCol="apple")*Weighting), so all my values are multiplied by the weighting column.
BUT
If there is FALSE, then the same, but WITHOUT the Weighting part, like:
=SUMPRODUCT(MyValues*(CriteriaCol="apple"))

One solution would of course be: =IF(ChB_Weighting=TRUE;SUMPRODUCT(MyValues*(CriteriaCol="apple")*Weighting);SUMPRODUCT(MyValues*(CriteriaCol="apple")))

But is there a more elegant one too? And especially, because there may be more Checkbox on the sheet, so it would be great, if one checkbox could turn on or off one single condition/array.

How would it be possible? Or what kind of solution would You recommend, to be able to make my sheet more flexible, and user friendly?

Any help is greatly appreciated!

Thank You All!

Best Wishes,
Zsolt (from Hungary <- This may be the reason for my typo mistakes, if there are some )

 

[Toadstool]

Hi Zsnemeth,

If treated mathematically TRUE is one and FALSE is zero so you could just multiple the Weighted % by the logical indicator, like this:

zsnemeth.xlsx
	A	B	C	D	E	F
1	MyValues	CriteriaCol	Weighting	Weighted		True or False
2	100	apple	25%	125		TRUE
3	120	apple	50%	180
4	200	apple	10%	220
5	160	apple	33%	212.8
6	220	apple	40%	308
7	140	apple	20%	168
8	80	orange	80%	0
9	80	apple	0%	80
10	80	apple	100%	160
Sheet1

Cell Formulas
Range	Formula
D2:D10	D2	=SUMPRODUCT([@MyValues]*([@CriteriaCol]="apple")*(1+(ChB_Weighting*[@Weighting])))

Named Ranges
Name	Refers To	Cells
ChB_Weighting	=Sheet1!$F$2	D2:D10
CriteriaCol	=Sheet1!$B$2:$B$10	D2
MyValues	=Sheet1!$A$2:$A$10	D2
Weighting	=Sheet1!$C$2:$C$10	D2

Cells with Data Validation
Cell	Allow	Criteria
F2	List	TRUE,FALSE

 

[zsnemeth]

Hi Toadstool,

Thank You very much for this very good solution.

However, I was not really able to implement it to my sheet, because if I only take 1 row as an example (just to ease the calculation-control), and this row has 50% in the weighting column, the result will be multiplied by 150%. (1+Weighting).
But: If I take away the "1+" part, it counts correctly, until the checkbox is checked, and the reference cell shows 1. Of course, after it turns to FALSE and 0, the whole result is at the end a big fat 0. (In this case it should simply show the 100% result).

And if I want to turn the question a little further, what if we want to turn a condition on or off, which has no numeric values (like weighting) in it, but a simple text.
Like: if I would say in Your example, I do want a second checkbox, which has to be able to select (I mean, of course the user) if the rows with "orange" need to be calculated with or without, in the end result?
As I could imagine, it would be something like this:
=SUMPRODUCT([@MyValues]*([@CriteriaCol]= _

If(ChB_Orange;"*";"<>orange") _ (< This part of the formula is only an example, how I mean: If second/separate checkbox is TRUE, then calculate with all values in Fruit, but if it is FALSE, then exclude "orange".)

*(1+(ChB_Weighting*[@Weighting])))

I hope, I explained it correctly.

Sorry for making it more difficult, but I think, that Your solution - which could actually be a perfect one for the case with numeric columns - does not really fit my original need (with the text columns too). My fault, but maybe now cleared it!

Thank You!

I really apprecciate Your efforts on this!

Cheers,
Zsolt

 

[Toadstool]

I had assumed you wanted the Weighted value to be the original with the weight applied. If you want to show the calculated weight separately then this may work for you. (I will now consider your additional question)

zsnemeth.xlsx
	A	B	C	D	E	F	G
1	MyValues	CriteriaCol	Weighting	Weight	WithWeightApplied		True or False
2	100	apple	25%	25	125		TRUE
3	120	apple	50%	60	180
4	200	apple	10%	20	220
5	160	apple	33%	52.8	212.8
6	220	apple	40%	88	308
7	140	apple	20%	28	168
8	80	orange	80%	0	80
9	80	apple	0%	0	80
10	80	apple	100%	80	160
2nd

Cell Formulas
Range	Formula
D2:D10	D2	=SUMPRODUCT([@MyValues]*([@CriteriaCol]="apple")*(ChB_Weighting*[@Weighting]))
E2:E10	E2	=[@Weight]+[@MyValues]

Named Ranges
Name	Refers To	Cells
'2nd'!ChB_Weighting	='2nd'!$G$2	D2:D10
'2nd'!CriteriaCol	='2nd'!$B$2:$B$10	D2
'2nd'!MyValues	='2nd'!$A$2:$A$10	D2:E2
'2nd'!Weight	='2nd'!$D$2:$D$10	E2
'2nd'!Weighting	='2nd'!$C$2:$C$10	D2

Cells with Data Validation
Cell	Allow	Criteria
G2	List	TRUE,FALSE

 

[Toadstool]

I think this addresses the additional requirement.

zsnemeth.xlsx
	A	B	C	D	E	F	G
1	MyValues	CriteriaCol	Weighting	Weight	WithWeightApplied		True or False
2	100	apple	25%	25	125		TRUE
3	120	apple	50%	60	180
4	200	apple	10%	20	220		Include Orange
5	160	apple	33%	52.8	212.8		TRUE
6	220	apple	40%	88	308
7	140	apple	20%	28	168
8	80	orange	80%	64	144
9	80	apple	0%	0	80
10	80	apple	100%	80	160
3rd

Cell Formulas
Range	Formula
D2:D10	D2	=SUMPRODUCT([@MyValues]*(OR($G$5,[@CriteriaCol]<>"orange"))*(ChB_Weighting*[@Weighting]))
E2:E10	E2	=[@Weight]+[@MyValues]

Named Ranges
Name	Refers To	Cells
'3rd'!ChB_Weighting	='3rd'!$G$2	D2:D10
'3rd'!CriteriaCol	='3rd'!$B$2:$B$10	D2
'3rd'!MyValues	='3rd'!$A$2:$A$10	D2:E2
'3rd'!Weight	='3rd'!$D$2:$D$10	E2
'3rd'!Weighting	='3rd'!$C$2:$C$10	D2

Cells with Data Validation
Cell	Allow	Criteria
G2	List	TRUE,FALSE
G5	List	TRUE,FALSE

 

[zsnemeth]

Hi Toadstool,

thank you for your solution.
I am trying to implement it to my sheet.

I'll tell you about success or fail.

Thanks!

Cheers,
Zsolt