Row count using more than a single criterion?

**TBrun** · 05-22-2013, 08:53 AM

Hello -

I have a spreadsheet (2007) and need to mark the first instance of a "1" occurring in one column, but only if the "1" is within a given range of numbers in another column.

I attached an example spreadsheet. In column D (highlighted yellow), I did what I need manually. Is there a formula that can be placed in D to do this automatically?

Example_MultCriteria.xls

Basically, each Person (column A) saw 10 Videos (column B) and they recognized something (a "Hit", column C). I want to mark the first point they recognized something in each video. Each video might have multiple Hits, but I only want to mark/flag the first Hit.

Thoughts?

**NBVC** · 05-22-2013, 09:03 AM

Try:

=IF(C2="","",--(COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,1)=1))

then format the results as Number, with Custom Type: 0;-0;;@

**daddylonglegs** · 05-22-2013, 09:05 AM

Try this formula in D2 copied down

=IF(AND(COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,1)=1,C2=1),1,"")

**Tony Valko** · 05-22-2013, 09:07 AM

Try this...

Entered in D2 and copied down:

=IF(C2="","",IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,1)=1,1,""))

**TBrun** · 05-22-2013, 09:08 AM

Those solutions worked, thank you both!

Row count using more than a single criterion?

LinkBack

Thread Tools

Rate This Thread

Display

Row count using more than a single criterion?

Re: Row count using more than a single criterion?

Re: Row count using more than a single criterion?

Re: Row count using more than a single criterion?

Re: Row count using more than a single criterion?

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions