How can I write this IF AND more clearly

[iamrufus]

Hi everyone

I have a column that calculates if 2 cells have a matching value and if they do it enters Match and if they don't it enters No Match. This works fine, what I am wanting to do is have a formula that looks at all the cells that have the value Match. The cell range with the values will always be the same G6:G7 and G12:G20.

The formula below is in a cell that calculates this for me and when all the above cells have the value Match it adds in the value Complete and until this value states complete it will have the value Not Complete. This does work fine but I can't help but think there must be a cleaner way to write it?

=IF(AND(G6="Match",G7="Match",G11="Match",G12="Match",G13="Match",G14="Match",G15="Match",G16="Match",G17="Match",G18="Match",G19="Match",G20="Match"),"Complete","Not Complete")

Appreciate any advice

 

[Joe4]

Try this:

=IF(AND(COUNTIF(G6:G7,"Match")=2,COUNTIF(G11:G20,"Match")=10),"Complete","Not Complete")