IF Statements and multiple columns

[Guest]

I want to add the number of times "Y" appears in a column, based upon the
date in another column. Here is my layout:

Col A Col B
Class Date Graduated?
8/4/2005 Y
8/4/2005 Y
8/4/2005 N
11/2/2005 Y
11/2/2005 Y

I want to know for how many who attended each class date, the number who
graduated .

This is what I tried
=IF(A1:A5,"8/4/2005",IF(B1:B5,"Y",CountA(B1:B5)))

It apepars at first to work (I get no error message), but I don't get a
value in the cell - I get #Value! error message instead.

I also tried:
=IF(A1:A5,"8/4/2005",COUNTIF(B1:b5,"Y"))

Same #Value! error in cell

Any help would be most appreciated!
Thanks,
Tina Hudson

 

[Rick Rothstein \(MVP - VB\)]

Assuming your data starts in Row 2 and the Cell C2 contains the date you
want to check (this allows you to make multiple queries by simply changing
its value), you could use this formula...

=SUMPRODUCT((A2:A1000=C2)*(B2:B1000="Y"))

Adjust the maxium range value (row 1000 here) to suit your data.

Rick

 

[Guest]

Rick,

Thanks! I also realized that since they all graduate (well, those who do),
graduate on the same date, I was able to do the following and get the same
result as your suggestion:
=COUNTIF(I11:I133,"9/22/2005")

However, what I now need is to know how many of those who graduated,
actually were licensed. Unlike with the graduation date, licensing can be on
different dates. So, should I just insert another column and type in "Y" for
those who get licensed and use your formula, or, is there a way to do this
with a date in the column. I'd like to keep the number of columns down as
these aren't the only columns in the spreadsheet.

Here is the full layout (first line of text is A2 and Dates are set up as
date fields)

Col A Col B Col C
Class Date Graduated Date Licensed Date
8/4/2005 9/22/2005 11/19/2005
8/4/2005 9/22/2005
8/4/2005
11/2/2005 12/4/2005 1/28/2006
11/2/2005

 

[Guest]

=SUMPRODUCT((B2:B1000=D2)*(C2:C1000<>""))

D2=Graduation date

 

[Guest]

Worked like a charm!
Have a good one