a more concise formula

**jwongsf** · 01-14-2009, 10:35 PM

Can anyone help to make this formula more concise and shorter, it was design to check a numbers in a range to see if any of them falls into a particular range.

=IF(SUM((COUNTIF(Fund,">11999")-(COUNTIF(Fund,">12999"))),(COUNTIF(Fund,">21099")-(COUNTIF(Fund,">28729"))),(COUNTIF(Fund,">28730")-(COUNTIF(Fund,">33999"))),(COUNTIF(Fund,">58999")-(COUNTIF(Fund,">59999"))),(COUNTIF(Fund,">82000")-(COUNTIF(Fund,">84999"))),(COUNTIF(FUND1,">11999")-(COUNTIF(FUND1,">12999"))),(COUNTIF(FUND1,">21099")-(COUNTIF(FUND1,">28729"))),(COUNTIF(FUND1,">28730")-(COUNTIF(FUND1,">33999"))),(COUNTIF(FUND1,">58999")-(COUNTIF(FUND1,">59999"))),(COUNTIF(FUND1,">82000")-(COUNTIF(FUND1,">84999"))))>0,"ATTACHMENT E IS REQUIRED","")

and if the any of the number fall into the range it will print the message, "Attachment is Required"

Thanks James

**JBeaucaire** · 01-14-2009, 11:39 PM

DCOUNT might help, look into it, or post your book up so we can see.

**Domenic** · 01-15-2009, 12:33 AM

Try...

=SUM((COUNTIF(Fund,">"&{11999,21099,28730,58999,82000})-COUNTIF(Fund,">"&{12999,28729,33999,59999,84999}))+(COUNTIF(Fund1,">"&{11999,21099,28730,58999,82000})-COUNTIF(Fund1,">"&{12999,28729,33999,59999,84999})))

Assuming that Fund refers to A2:A10, and Fund1 refers to C2:C10, here's another way...

=SUM(COUNTIF(OFFSET(Fund,,{0,2},,1),">"&{11999;21099;28730;58999;82000})-COUNTIF(OFFSET(Fund,,{0,2},,1),">"&{12999;28729;33999;59999;84999}))

Note, however, OFFSET is a volatile function.

Hope this helps!