SUMPRODUCT values from different locations if checkbox

indygo

Board Regular
Joined
Dec 2, 2013
Messages
119
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:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,434
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
119
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,434
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
119

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,434
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
119

ADVERTISEMENT

amazing stuff, thanks a lot! :cool:
 

lrobbo314

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

indygo

Board Regular
Joined
Dec 2, 2013
Messages
119
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,434
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,463
Messages
5,831,783
Members
430,088
Latest member
meagerd

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