multiple countif function with text

[stuuk2008]

Hi all this is my first post so please be gentle.

What i am trying to do is to create a register in excel that counts the number of times a person has an incident at work which i have done. The problem that I have is that I want it to not only count the total number of incident but to count the incidents only within the last year and when they are at fault.

So as an example my spreadsheet currently looks like this

A B C
name date at fault
1 stu 1/2/08 y
2 stu 1/2/07 Y
3 stu 1/1/08 N


What i need it to do is say that person stu has 1 incident to date as one is not at fault and the other is over 1 year ago. Any ideas would be great..thanks

[NBVC]

Try:

=Sumproduct(--(A2:A100="stu"),--(Year(B2:B100)=2008),--(C2:C100="y"))

or

if you mean with 365 days of today...

=Sumproduct(--(A2:A100="stu"),--(Today()-B2:B100<=365),--(C2:C100="y"))


all variables can be replaced with cell references containing those variables.

adjust ranges to suit.

[stuuk2008]

worked perfectly mate thanks very much..just couldnt get my head round it.


I ended up using this formula and changed it to my needs..can you please explain how excel reads it so I know in future


=Sumproduct(--(A2:A100="stu"),--(Today()-B2:B100<=365),--(C2:C100="y"))