Microsoft Office Forums Countif with 2 criteria
 Register FAQ Search Today's Posts Mark Forums Read

#1
05-22-2011, 09:31 AM
 ibrahimaa Windows Vista Office 2007 Advanced Beginner Join Date: May 2011 Posts: 35
Countif with 2 criteria

=COUNTIF(\$G\$2:\$G\$19,G8)=1

I want to edit the above formula to inject AND to make 2 criteria

This did not work: =COUNTIF(AND((\$G\$2:\$G\$17,G8)=1)G8 <>"")

What I want to happen is for the cell not to accept any input if G8 is not repeated and/or blank.
#2
05-23-2011, 02:06 AM
 Colin Legg Windows 7 32bit Office 2010 32bit Expert Join Date: Jan 2011 Location: UK Posts: 369

Hi,
Quote:
 What I want to happen is for the cell not to accept any input if G8 is not repeated and/or blank.
I don't fully understand what you mean by not to accept any input if G8 is not repeated'. At the moment you are checking if G8 occurs once:
=COUNTIF(\$G\$2:\$G\$19,G8)=1

but, since G2:G19 intersects G8, for G8 to be repeated, shouldn't the check be:
=COUNTIF(\$G\$2:\$G\$19,G8)>1

?

Depending on that, I think you want a data validation formula like this:
Code:
`=AND(COUNTIF(G2:G17,G8)>1,NOT(ISBLANK(G8)))`
If I misunderstood the G8 repeating logic then just adjust the COUNTIF() part of the formula.
#3
05-23-2011, 05:50 AM
 ibrahimaa Windows Vista Office 2007 Advanced Beginner Join Date: May 2011 Posts: 35
Countif mutiple criteria

Thanks, let me rephrase my problem:

I need to enter data in columns H where this formula is needed and column G where criteria has to be met. Column G for example has rows for data validation from 2 to 19:

First criteria - H should not accept the data (which is numerical) if the value in G2 until G19 is duplicated. The value in G column must only entered once

Second criteria - H should not accept the data if value of column G is blank.

I hope this is clearer now. Thanks.

=COUNTIF(\$G\$2:\$G\$19,G8)=1

but, since G2:G19 intersects G8, for G8 to be repeated, shouldn't the check be:
=COUNTIF(\$G\$2:\$G\$19,G8)>1
#4
05-23-2011, 11:23 AM
 Colin Legg Windows 7 32bit Office 2010 32bit Expert Join Date: Jan 2011 Location: UK Posts: 369

Hi,

Data validation in H2 would be:
Code:
`=AND(COUNTIF(\$G\$2:\$G\$19,\$G2)=1,NOT(ISBLANK(\$G2)))`
When you enter the validation formula, untick the "Ignore Blank" checkbox.

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Nancy Grams Outlook 0 11-12-2009 03:03 PM p0k Excel 1 10-22-2009 08:33 PM AdamNT Excel 1 08-11-2006 11:51 PM pumpkin head Excel 1 02-17-2006 09:06 AM jvieille Misc 1 02-11-2006 06:26 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:22 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top