SUMPRODUCT condition of an array

zsnemeth

New Member
Joined
Mar 4, 2015
Messages
36
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 ;) )
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
ABCDEF
1MyValuesCriteriaColWeightingWeightedTrue or False
2100apple25%125TRUE
3120apple50%180
4200apple10%220
5160apple33%212.8
6220apple40%308
7140apple20%168
880orange80%0
980apple0%80
1080apple100%160
Sheet1
Cell Formulas
RangeFormula
D2:D10D2=SUMPRODUCT([@MyValues]*([@CriteriaCol]="apple")*(1+(ChB_Weighting*[@Weighting])))
Named Ranges
NameRefers ToCells
ChB_Weighting=Sheet1!$F$2D2:D10
CriteriaCol=Sheet1!$B$2:$B$10D2
MyValues=Sheet1!$A$2:$A$10D2
Weighting=Sheet1!$C$2:$C$10D2
Cells with Data Validation
CellAllowCriteria
F2ListTRUE,FALSE
 
Upvote 0
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
 
Upvote 0
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
ABCDEFG
1MyValuesCriteriaColWeightingWeightWithWeightAppliedTrue or False
2100apple25%25125TRUE
3120apple50%60180
4200apple10%20220
5160apple33%52.8212.8
6220apple40%88308
7140apple20%28168
880orange80%080
980apple0%080
1080apple100%80160
2nd
Cell Formulas
RangeFormula
D2:D10D2=SUMPRODUCT([@MyValues]*([@CriteriaCol]="apple")*(ChB_Weighting*[@Weighting]))
E2:E10E2=[@Weight]+[@MyValues]
Named Ranges
NameRefers ToCells
'2nd'!ChB_Weighting='2nd'!$G$2D2:D10
'2nd'!CriteriaCol='2nd'!$B$2:$B$10D2
'2nd'!MyValues='2nd'!$A$2:$A$10D2:E2
'2nd'!Weight='2nd'!$D$2:$D$10E2
'2nd'!Weighting='2nd'!$C$2:$C$10D2
Cells with Data Validation
CellAllowCriteria
G2ListTRUE,FALSE
 
Upvote 0
I think this addresses the additional requirement.

zsnemeth.xlsx
ABCDEFG
1MyValuesCriteriaColWeightingWeightWithWeightAppliedTrue or False
2100apple25%25125TRUE
3120apple50%60180
4200apple10%20220Include Orange
5160apple33%52.8212.8TRUE
6220apple40%88308
7140apple20%28168
880orange80%64144
980apple0%080
1080apple100%80160
3rd
Cell Formulas
RangeFormula
D2:D10D2=SUMPRODUCT([@MyValues]*(OR($G$5,[@CriteriaCol]<>"orange"))*(ChB_Weighting*[@Weighting]))
E2:E10E2=[@Weight]+[@MyValues]
Named Ranges
NameRefers ToCells
'3rd'!ChB_Weighting='3rd'!$G$2D2:D10
'3rd'!CriteriaCol='3rd'!$B$2:$B$10D2
'3rd'!MyValues='3rd'!$A$2:$A$10D2:E2
'3rd'!Weight='3rd'!$D$2:$D$10E2
'3rd'!Weighting='3rd'!$C$2:$C$10D2
Cells with Data Validation
CellAllowCriteria
G2ListTRUE,FALSE
G5ListTRUE,FALSE
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,222,437
Messages
6,166,026
Members
452,008
Latest member
Customlogoflipflops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top