SUMPRODUCT values from different locations if checkbox

indygo

Board Regular
Joined
Dec 2, 2013
Messages
117
Hi again, the best excel help in the internet! :wink:

Ok on the left hand side we have statistics for different people. There are average values, sums etc.
On the right hand side we have summary of the whole group. Now I'm using boxes to decide which people to sum up to see the differences and impact.
I've tried this code =SUMPRODUCT(($A:$A=TRUE)*$C$3, $C$7, $C$11)) but apparently this command "sumproduct" works only if rows are equal, so it's impossible to decide which cells are being calculated. Is there any way please? I'm sure there is, my excel knowledge is just too limited...

Cheers


PS. In previous tread somebody adviced me to use =SUMPRODUCT(($A:$A=TRUE)*$C$3*$C$7*$C$11)) as correct code but I was receiving bad calculation. Cells in Red should give me total of 342 and it gives me 696000 instead?
If I tick first box it gives my 232000 instead of 232.




Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
K​
1​
Daisy​
TOTAL Together​
2​
FALSE​
Sum​
W​
BP​
Sum​
W​
BP​
3​
Total​
464​
232​
154​
78​
0​
4​
Average​
11​
11​
7,3​
3,7​
5​
John​
6​
FALSE​
Sum​
W​
BP​
7​
Total​
121​
100​
20​
1​
8​
Average​
15​
10​
30​
5​
9​
Frank​
10​
FALSE​
Sum​
W​
BP​
11​
Total​
69​
10​
50​
9​
12​
Average​
27,3​
45​
35​
2​
13​
14​

<tbody>
</tbody>



real photo:

VvvbMzu.jpg

 
Last edited:

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,029
Office Version
  1. 365
Platform
  1. Windows
I wouldn't reference the entire column unless you absolutely have to. It's bad practice and it will significantly slow down calculation speeds. Especially with array formulas, which the following is.

Put in I3 and use Control+Shift+Enter to confirm formula.

Code:
=SUM(IF($A$2:$A$12=TRUE,OFFSET($C$2:$C$12,1,0),0))
 

indygo

Board Regular
Joined
Dec 2, 2013
Messages
117
Thanks for fast reply. What does the ending part mean ",1,0),0)) ? it's a mystery to me.

I tried it and:

AHZSEp8.jpg
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,029
Office Version
  1. 365
Platform
  1. Windows
I am on a computer that doesn't allow me to see the pictures you post here. You have Trues and Falses in column A. In column C, you have the values to be summed. But, each of the values in column C are 1 row below the trues and falses in column A. The ending part of the formula that you are referring to is part of an Offset function telling it that if it finds a true in column A, look in column C 1 row down for the value.

The other ,0 is the end part of the if statement. Essentially: Sum(If Column A = True, Then Offset column C by 1 row, Otherwise add 0 to total sum)
 

indygo

Board Regular
Joined
Dec 2, 2013
Messages
117

ADVERTISEMENT

I am on a computer that doesn't allow me to see the pictures you post here. You have Trues and Falses in column A. In column C, you have the values to be summed. But, each of the values in column C are 1 row below the trues and falses in column A. The ending part of the formula that you are referring to is part of an Offset function telling it that if it finds a true in column A, look in column C 1 row down for the value.

The other ,0 is the end part of the if statement. Essentially: Sum(If Column A = True, Then Offset column C by 1 row, Otherwise add 0 to total sum)


I've managed to put that code again and it got accepted without an error but it gives me value 0, no matter which box I tick :(

=SUM(IF($A$2:$A$12=TRUE,OFFSET($C$2:$C$12,1,0),0))
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,029
Office Version
  1. 365
Platform
  1. Windows
Did you use Control+Shift+Enter?

Go to cell I3, edit the cell (hit F2) and then hit Control+Shift+Enter.
 

indygo

Board Regular
Joined
Dec 2, 2013
Messages
117

ADVERTISEMENT

amazing stuff, thanks a lot! :cool:
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,029
Office Version
  1. 365
Platform
  1. Windows
No problem. Glad it worked for ya.
 

indygo

Board Regular
Joined
Dec 2, 2013
Messages
117
One more thing, as I almost forgot. Since this OFFSET thing is quite tricky.
What logic I should apply to summary other values. For example average - which is just one row below. I can't just drag like with other functions- I have to type code again right?
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,029
Office Version
  1. 365
Platform
  1. Windows
Should just be

Code:
=SUM(IF($A$2:$A$12=TRUE,OFFSET($C$2:$C$12,[B]2[/B],0),0))

The difference being the 2 in bold above. Offsetting it by 2 rows instead of 1.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,862
Messages
5,598,500
Members
414,243
Latest member
Shockpulsar

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
Top