Array formula question

[Richard Buttrey]

I have an array formula of the usual form. i.e.

In say Cell B30 I have

=SUM(('Data'!D109999=A30)*('Data'!C10:C9999=test_cell)*('Data'!G10:G9999))

Where A 30 is a string value on the same sheet as the formula, and
test_cell is another cell on another sheet.

Everything is fine where A30 exists in the Data Sheet Range D109999.
However when the string in A30 does not exist, B30 returns a #Value!

How can I modify the array formula so that it evaluates to zero if A30
does not exist in the Data Sheet column D? I've tried building in an
If...Lookup formula test to see if it exists, as I would normally do
in an non array formula, but an array formula does not seem to like
this.

Has anyone any suggestions please.

Usual TIA

Rgds


Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(('Data'!D109999=A30)*('Data'!C10:C9999=test_cell),'Data'!
G10:G9999)

 

[Tom Ogilvy]

It returns zero for me under those conditions (no rows matching the
specified condition(s))

however, perhaps it has something to do with the data you have in the sheet.
If you want to check for no match you could do

=IF(COUNTIF(Data!D109999,A30)=0,0,SUM((Data!D109999=A30)*(Data!C10:C9999
=Test_Cell)*(Data!G10:G9999)))

or use match

=IF(ISERROR(MATCH(A30,Data!D109999,0)),0,SUM((Data!D109999=A30)*(Data!C1
0:C9999=Test_Cell)*(Data!G10:G9999)))

Enter either with Ctrl+Shift+Enter and it should work

 

[Richard Buttrey]

It returns zero for me under those conditions (no rows matching the
specified condition(s))

Tom and Frank,

Thank you both very much for your swift response.
Your answers, suggesting that it should work caused me to examine the
process I'd gone through in creating the formula. I now realise what
my mistake was. I'd created the formula with a valid string but forgot
to make the Data sheet range, or the test cell range, an absolute
reference. Consequently when I copied the good formula to another row,
the ranges had changed. Doh!

Kind Regards,

Richard

Richard Buttrey
Grappenhall, Cheshire, UK
__________________________