Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-22-2011, 09:31 AM
ibrahimaa ibrahimaa is offline Countif with 2 criteria Windows Vista Countif with 2 criteria Office 2007
Advanced Beginner
Countif with 2 criteria
 
Join Date: May 2011
Posts: 35
ibrahimaa is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 05-23-2011, 02:06 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Countif with 2 criteria Windows 7 32bit Countif with 2 criteria Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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.
Reply With Quote
  #3  
Old 05-23-2011, 05:50 AM
ibrahimaa ibrahimaa is offline Countif with 2 criteria Windows Vista Countif with 2 criteria Office 2007
Advanced Beginner
Countif with 2 criteria
 
Join Date: May 2011
Posts: 35
ibrahimaa is on a distinguished road
Default 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
Reply With Quote
  #4  
Old 05-23-2011, 11:23 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Countif with 2 criteria Windows 7 32bit Countif with 2 criteria Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Criteria in Outlook Search Nancy Grams Outlook 0 11-12-2009 03:03 PM
Countif with 2 criteria Sort by criteria: number of results p0k Excel 1 10-22-2009 08:33 PM
How to count cells containing data and meet certain criteria AdamNT Excel 1 08-11-2006 11:51 PM
Countif with 2 criteria Multiple criteria in SUMIF? pumpkin head Excel 1 02-17-2006 09:06 AM
Countif with 2 criteria Filtering past or future events in DRW query criteria jvieille Misc 1 02-11-2006 06:26 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:37 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft