We help IT Professionals succeed at work.

# Excel 2007 formula req'd to identify a formula that returns a specific result in a range.

on
Medium Priority
348 Views
I have a formula (thanks to Barry!)  that reports a result (text = 'imcomplete') when the preceding 5 cells in a row are not all filled out. The table may have no results of "incomplete" visible (in column 6 of my table) or one or more showing in my table. I want to be able to present a visible clue at the top of the sheet that shows unfinished records. I don't need anything smart, I just want cell F2 to show the text "INCOMPLETE ENTRIES EXIST BELOW" when 1 or more cells return the message "imcomplete" in the range F10:F100. I tried using the counta command, but a formula exists in every cell and it finds every cell not blank. Any ideas?

TIA
Comment
Watch Question

## View Solutions Only

CERTIFIED EXPERT
Most Valuable Expert 2013
Commented:
Try using COUNTIF this tiime, i.e. in F2

=IF(COUNTIF(F10:F100,"Incomplete")>0,"INCOMPLETE ENTRIES EXIST BELOW","")
CERTIFIED EXPERT

Commented:
=if(countif(F10:F100,"Incomplete")>0,"INCOMPLETE ENTRIES EXIST BELOW","")
CERTIFIED EXPERT

Commented:
Apologies, crossing posts.

Commented:
I tried that result, but even when there are no results = "Incomplete", the result "INCOMPLETE ENTRIES EXIST BELOW" appears.

I tried your formula off to the side and it works when I type the text manually - something about the formula result??

CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
Are you sure you used COUNTIF not COUNTA?

=IF(COUNTIF(F10:F100,"Incomplete")>0,"INCOMPLETE ENTRIES EXIST BELOW","")

regards, barry
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
See example attached

"x" in B17 means that F17 shows "Incomplete" so F2 shows as "INCOMPLETE ENTRIES EXIST BELOW". If you delete that "x" then F2 becomes blank. Is that what you needed?

regards, barry
27426645.xls

Commented:
Yes, I just copied and pasted your formula.
Pls see screen prints provided

sample formula in column M: =IF(OR(COUNTA(A25:L25)={0,6}),"","<== INCOMPLETE!")

Thx.

XL-capture.JPG
CERTIFIED EXPERT

Commented:
ShepVillage,

Put a ~ in front of the < character.

Regards,
Brian.
CERTIFIED EXPERT

Commented:
CERTIFIED EXPERT

Commented:
=IF(OR(COUNTA(A25:L25)={0,6}),"","=<== INCOMPLETE!")
CERTIFIED EXPERT

Commented:
Past time for my bed - please ignore my silly posts!
CERTIFIED EXPERT
Most Valuable Expert 2013
Commented:
Hello ShepVillage,

I think Brian has it half right. Beacuse you have used <= in the text that's throwing off COUNTIF (it's treating <= as "less than or equal to", so assuming that M25 copied down has this formula

=IF(OR(COUNTA(A25:L25)={0,6}),"","<== INCOMPLETE!")

You can leave that "as is" but change M24 formula to this

=IF(COUNTIF(M25:M50,"=<== INCOMPLETE!")>0,"INCOMPLETE ENTRIES EXIST BELOW","")