# Simple (I Think) Array - Crt-Shift-Enter

#### Kavy

##### Well-known Member
Hello,

Thank for you any tips on this first off!

I am trying to enter a formula using an array (i.e. hit crt-shift-enter when you enter it instead of enter). I have one cell which sums up the total \$ from 4 other cells in a collumn. Now, some of the rows may require an extra tax on them (GST), which the user enters the rate in one cell above. Beside each row I have a Yes/No combo box and I only want to and in the tax for the cells that have a yes there. I could use a bunch of IF statments since I only have 4 rows but I would like to advoid that

If anyone knows a better way of doing this with regular formula please tell me.

I currently have the formula below, it doesnt work. It only checks the first yes/no and uses that as a logic test for the entire array.

<code>
{=IF(D2:D7="Yes",SUM(C2:C7))}
</code>

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Jonmo1

##### MrExcel MVP
The correct syntax would be

=SUM(IF(D2:D7="Yes",C2:C7))

Array entered with CSE.

But, you don't need the array type formula for that

=SUMIF(D2:D7,"Yes",C2:C7))

But if you're just giving a dummed down version of a more complex formula, say you have multiple criteria, you can use sumproduct which does not require CSE

=SUMPRODUCT(--(D2:D7="Yes"),--(anotherRange=anothercriteria),C2:C7)

IMPORTANT
Ranges cannot be entire column refs like D:D in the sumproduct formula.
Must use Row #s like D2:D7 - unless in XL2007
Ranges must be same size

Hope that helps...

##### MrExcel MVP
Hello,

Thank for you any tips on this first off!

I am trying to enter a formula using an array (i.e. hit crt-shift-enter when you enter it instead of enter). I have one cell which sums up the total \$ from 4 other cells in a collumn. Now, some of the rows may require an extra tax on them (GST), which the user enters the rate in one cell above. Beside each row I have a Yes/No combo box and I only want to and in the tax for the cells that have a yes there. I could use a bunch of IF statments since I only have 4 rows but I would like to advoid that

If anyone knows a better way of doing this with regular formula please tell me.

I currently have the formula below, it doesnt work. It only checks the first yes/no and uses that as a logic test for the entire array.

<CODE>
{=IF(D2:D7="Yes",SUM(C2:C7))}
</CODE>

Just...

=SUMIF(D2:D7,"Yes",C2:C7)

would suffice.

#### Kavy

##### Well-known Member
ha.... wow... didn't know about sumIF

Thank you both for the replys and thank you jonmo1 for the alt if I have more critiera!

Kavy

Replies
12
Views
289
Replies
3
Views
102
Replies
6
Views
48
Replies
3
Views
232
Replies
5
Views
159

### Forum statistics

1,191,485
Messages
5,986,860
Members
440,055
Latest member
CraigTriesHisBest

### 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