COUNTIF - Depending On Various Factors?

[StuFromSaturday]

Hi all, wonder if you can help me out.

I've had a good look through the site and can't quite find an answer to this one I'm afraid. I have two columns of data; A contains the daily dates from 31/12/1999 to date, in that format, and B contains various values, but only on working weekdays.

Now, what I need to be doing in column C, is adding a COUNTIF that counts the number of non-zero values in column B, during the current year of column A. Or, to put it another way, how many days during the year specified in A1 is there a value in B (so the result, dragged down, will be something like 252, 252, 252, 252 and then once the year increases by one 251, 251, 251, 251).

I hope I've explained that all ok and that someone can help - I did see something about SUMIFS, but I don't have that option I'm afraid.

I'm using Excel 2003. Any help gratefully received.


Regards,


Stu

 

[njimack]

=SUMPRODUCT(--(YEAR(A2:A100)=YEAR(A1)),--(B2:B100>0))

You won't be able to refer to entire columns (e.g A:A) so either change the ranges as required or use dynamic ranges.

 

[StuFromSaturday]

You don't hang about, do you?!

Superb, thank you...I'm gonna spend a while now getting my head round why it works, but thank you very much indeed!