Returning a 0 finding when multible criterion does not match

**PTMD** · 04-16-2012, 07:32 PM

Hi Everybody,
This is my first post and I need your assistance. I have a spreadsheet created in Excel 2003 (see attached) that seems to work OK as long as at least 1 field of A2:A4 is tagged as Open. The object being for a number to appear in F2
What I would like to achieve if it is possible, is to extent the formula for F2 to return a 0 value if all A2:A4 fields equal Closed.

Hope you can help
Steve

**TMS** · 04-16-2012, 07:58 PM

=IF(COUNTIF(A2:A4, "closed")=3,0,SUMIF(...

**PTMD** · 04-16-2012, 09:41 PM

TMShucks,
Thanks for the quick reply but I must be doing something wrong. I have entered your formula (I think) but do not seem to be getting the right result.
The system wants to add an additional ')' at the end of the formula I eneter and then returns a value of #DIV/0! (again).

Steve

**PTMD** · 04-17-2012, 02:27 AM

Hey Everybody,
Just found the answer to my problem. Here is the formula: =IF((E2=0),0,(SUMIF(A2:A4,"Open",D2:D4)/E2))

Thanks for your help
Steve

**TMS** · 04-17-2012, 02:50 AM

Thanks for sharing the answer.

You could modify my suggestion and eliminate E2 from the calculation:

=IF(COUNTIF(A2:A4,"open")=0,0,SUMIF(A2:A4,"Open",D2:D4)/COUNTIF(A2:A4,"Open"))

Note: apologies for assuming they'd be all "closed" ... I took the original post literally

the formula for F2 to return a 0 value if all A2:A4 fields equal Closed