SUMPRODUCT values from different locations if checkbox

indygo

Board Regular
Joined
Dec 2, 2013
Messages
126
Office Version
  1. 365
Platform
  1. Windows
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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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))
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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))
 
Upvote 0
Did you use Control+Shift+Enter?

Go to cell I3, edit the cell (hit F2) and then hit Control+Shift+Enter.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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