Count ocurrences based in another cell

[arturpericles]

Hello!


I'm really sorry that the first post I make here is a request, but I have no other choice. I'm stuck with a problem in for my essay (my essay is actually on constitutional law, so this is not cheating, heh). I'll try to make it look as simple as possible.


I have a sheet that goes something like this:

A B C D

1 Date Name Cats Dogs

2 10/10/11 John 1 0

3 10/10/11 Bob 2 1

4 10/10/11 John 1 1

5 11/10/11 Tom 1 1


What I need to know is: 1. For a given day: did anyone have at least 1 cat? Did anyone have at least one dog (and so on)? 2. For a given person on a given day: did he (or she) have at least one dog, or cat? My goal, actually, is to know, for the entire time period, on how many days did at least one person have at least one cat, or dog?


Problem is: though the number of people is limited (11), the number of dates entered (in reality, judicial cases) is too vast (77) as is the number of rows in the sheet (260). Each row refers to a date (in reality, a case), but more than one row many refer to the same case, as the number of cats and dogs might change throughout the date. So John could have no dogs at 10am, but get one at 5pm (in reality, this refers to pages in court transcripts).


Any ideas?! =)


Thanks in advance!

 

[Luke M]

Since your original data has different records for different times of day, I recommend creating a short list of the dates concerned (Maybe, Oct 1 - Oct 31?)

I'll assume this list is in E2:E32


To just check for cats on specific date:

=SUMIF(A:A,E2,C:C)>0

Just check for dogs on specific date:

=SUMIF(A:A,E2,D:D)>0

Check for cat or dog

=SUMPRODUCT(--($A$2:$A$50=E11),$C$2:$C$50+$D$2:$D$50)>0


Check for cat or dog for specific person on specific date:

=SUMPRODUCT(--($A$2:$A$50=E11),--($B$2:$B$50=Name),$C$2:$C$50+$D$2:$D$50)>0


Now you should be able to quickly see how many days someone had at least 1 dog/cat. If you need a formula:

=COUNTIF(F:F,TRUE)