COUNTIF? SUMIF? SUMPRODUCT? IF?

[omss]

hi,

Here is a data:

Month completed Year completed Past Due? Name
7 2007 Y
Joe
7 2007 N
Allie
7 2007 Y
Allie
7 2007 N
John
8 2007 N
Mark
8 2007 N
Amanda
9 2007 Y
John
10 2007 Y
Amanda
11 2007 Y
John
11 2007 N
Allie
4 2008 Y
Joe
3 2008 N
Mark
6 2008 N
Amanda
6 2008 Y
Amanda
5 2008 N
Allie
1 2008 N
John

I was wondering if I could have a function that would sort the number of
completions done per month FOR THE FISCAL YEAR 2008 ONLY where the fiscal
year starts from November1st. So for ex. the fiscal year of 2008 starts on
November 1st 2007. So, like(according to the list above i need to have the
following for "Number of Reviews for FY2008"):

Month Number of Reviews for FY2007 Number of Reviews for
FY2008
Nov 0 2
Dec 0 0
Jan 0 1
Feb 0 0
Mar 0 1
Apr 0 1
May 0 1
Jun 0 2
Jul 4 -
Aug 2 -
Sep 1 -
Oct 1 -

so far I have =IF($D:$D=2007,COUNTIF(B:B,11),0)
where column B and D are the Month and Year completed respectively. It works
for half of the months but the other half it doesn't

Secondly, I would like to make another list that would sort it so that it
tells the number of past dues done per month. So:

Month Past Dues for Fiscal Year 2008
Nov 1
Dec 0
Jan 0
Feb 0
Mar 0
Apr 1
May 0
Jun 1
Jul 0
Aug 0
Sep 0
Oct 0

i tried all these:

=IF(AND(B:B=7,D=2007),COUNTIF(E:E,Y),0)

=COUNT(AND(COUNTIF(B:B,7),COUNTIF(D,2007),COUNTIF(E:E,Y)))

=(IF(AND(E:E="Y",D=2007),0,COUNTIF(B:B,7)))

=SUM(--(D=2007),--(B:B=7),--(E:E="Y"))

=IF(E:E="Y",0,IF(D=2007,COUNTIF(B:B,7),0))

=SUM((B:B<>7)*(D<>2007)*(E:E="Y"))

and a lot of other things. I just can't seem to figure out the Past Dues
one. I need to have a CountIF with multiple criterias, or a sumproduct or
something like that

help please!
appreciate it!
omss

 

[Teethless mama]

Try like this:

SUMPRODUCT((B1:B100=7)*(D1100=2007)*(E1:E100="Y"))

You can't use a whole column, unless you are using xl-2007

 

[Herbert Seidenberg]

Here is the finished spreadsheet.
Only two formulas.
http://www.freefilehosting.net/download/3bg76