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

[Kavy]

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>

Thank you for any advice!

 

[Jonmo1]

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

 

[Aladin Akyurek]

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>

Thank you for any advice!

Just...

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

would suffice.

 

[Kavy]

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