Aug 07 2018 02:48 AM - edited Aug 07 2018 02:50 AM
I 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?
Aug 07 2018 03:05 AM
SolutionHi 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).
Aug 07 2018 03:07 AM
OMG. Of Course! In my haste I totally forgot to do that.
Aug 07 2018 03:05 AM
SolutionHi 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).