SOLVED

SUM formula using array calculates but doesn't populate into cell

Copper Contributor

WorksheetWorksheet

Formula result in worksheetFormula result in worksheetFormula result in the formula arguments windowFormula result in the formula arguments windowI have a sum formula in excel that I am using to calculate the total $ in column but filtering using an IF statement and a ISNUMBER(SEARCH) to look up a corresponding name as an array and comparing the string to a concatenated list of names.


The formula looks like this:

 

=SUM(IF(ISNUMBER(SEARCH(G8:G16,$C$9)),H8:H16,0))

 

When I review the formula in the formula arguments I get the correct result $54610 (see screenshot). But in the worksheet it populates as null (this is also true for any aggregation (max, count, etc)

 

Not sure if I've got something wrong, but I've never seen something calculate in one and not populate in the other.

 

Any ideas?

 

 

2 Replies
best response confirmed by Peter Lang (Copper Contributor)
Solution

Hi Peter,

 

That shall be array formula, entering it into D29 use Ctrl+Shift+Enter instead of Enter (press Ctrl and Shift, hit Enter, release all 3).

OMG. Of Course! In my haste I totally forgot to do that.

1 best response

Accepted Solutions
best response confirmed by Peter Lang (Copper Contributor)
Solution

Hi Peter,

 

That shall be array formula, entering it into D29 use Ctrl+Shift+Enter instead of Enter (press Ctrl and Shift, hit Enter, release all 3).

View solution in original post