# SUMPRODUCT values from different locations if checkbox

#### indygo

##### Board Regular
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:

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
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
Thanks for fast reply. What does the ending part mean ",1,0),0)) ? it's a mystery to me.

I tried it and:

#### lrobbo314

##### Well-known Member
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

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
Did you use Control+Shift+Enter?

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

#### indygo

##### Board Regular

amazing stuff, thanks a lot!

#### lrobbo314

##### Well-known Member
No problem. Glad it worked for ya.

#### indygo

##### Board Regular
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
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.

Replies
1
Views
44
Replies
11
Views
347
Replies
6
Views
202
Replies
11
Views
127
Replies
6
Views
153

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.

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.

### Which adblocker are you using?

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

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