Can you use arrays within other non array formulas?

**beesus311** · 10-04-2013, 11:10 AM

Hi.

I'm using this formula in a cell: {=SUM(SUMIF(BK44:BK53,"*"&PAYEES!$G$2:$G$200&"*",BL44:BL53))}

I want to add the result to the result of another formula, which is not an array.

Can I do this within the same cell? I obviously can't bracket this off as normal cause of the weird {} array bracketing.
I don't really want to bounce the result into another cell. I want to keep it contained in one cell.

What is the way to do this?

**benishiryo** · 10-04-2013, 11:15 AM

hi there. the curly brackets are not manually keyed in, so it's fine to put in another formula & then press CTRL + SHIFT + ENTER again. if you click on the formula bar of your current formula, you won't see the curly brackets. so if you add a formula, it will automatically become this after pressing CSE
{=SUM(SUMIF(BK44:BK53,"*"&PAYEES!$G$2:$G$200&"*",BL44:BL53))+SUM(A1:A2)}

and by the way, you can avoid CSE by using SUMPRODUCT instead of SUM
=SUMPRODUCT(SUMIF(BK44:BK53,"*"&PAYEES!$G$2:$G$200&"*",BL44:BL53))

**beesus311** · 10-04-2013, 12:26 PM

Let me rephrase the question. I wish to write this:
={SUM(SUMIF(BK44:BK53,"*"&PAYEES!$G$2:$G$200&"*",BL44:BL53))}*A4
(Which I understand is impossible to type, but that's the order I need the calculation - Calculate the array and then multiply by A4)

but it ends up like this:
{=SUM(SUMIF(BK44:BK53,"*"&PAYEES!$G$2:$G$200&"*",BL44:BL53))*A4}

which isn't the same thing. As far as I can work out it is multiplying all values in the array by A4 and the summing.

How do I change the order of calculation if I can't position the brackets where I want to?