Newbie question on formula's

[Wikked21]

I have a worksheet that contains several columns of data that I want to sort and/or compute figures. I have figured out how to accomplish everything except this last item.

I have a column that has enrollment dates and another that has a credit status. The status column can be "Y" or "N".

What I want the formula to do is provide the answer to "How many people have a 'Y' status <credit> with an Enrollment date after xx/xx/xx."

Any help would be greatly appreciated. I'm sure this is easy but it is driving me nuts.

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(A1:A100="Y"),--(B1:B100>DATE(2004,1,30)))

 

[CLR]

This information can also be viewed by using the AutoFilter........Data >
Filter > AutoFilter........and then setting the criteria on your date column
as "custom" and "greater than or equal to " your target date, and then
selecting "Y" in the status column...................this method allows you
to actually view the filtered data rather than just getting a count of
it.........sometimes a benefit........

Vaya con Dios,
Chuck, CABGx3

I have a worksheet that contains several columns of data that I want to

sort and/or compute figures. I have figured out how to accomplish
everything except this last item.

I have a column that has enrollment dates and another that has a credit

status. The status column can be "Y" or "N".

What I want the formula to do is provide the answer to "How many people

Any help would be greatly appreciated. I'm sure this is easy but it is

driving me nuts.

 

[Wikked21]

Thanks for the help so far but the formula from Frank doesn't seem to work and although I am aware of and use the AutoFilter it is not appropriate in this case.

The SUMPRODUCT formula just didn't seem to work. I'm not sure if I explained this correctly or not <probably not!> but I'll try again:

Each individual may earn a credit <"Y" or "N"> for a category. Each individual has a different Enrollment date in the program. Both items of data are represented in their own columns.

I want to find the total number of individuals in the "pool" of having enrolled after xx/xx/xxxx. That I have done with the COUNTIF function.

I then want to know how many of these same individuals <enrolled after xx/xx/xxxx> were successful <or have "Y" in the credit column>. What I am looking for from the COUNTIF is "7" <in the 'pool'> and from the formula I need help with "5" <successful or having 'Y' in the credit column>. I can then see the percentage, match it with our goals, determine percentage of goal met, etc.

Again, any and all help is appreciated. Just for my own knowledge, what do the -- represent in the SUMPRODUCT formula?

Thanks again.

 

[Frank Kabel]

Hi
what is the exact formula you have used.
Also please provide some example rows / column information (in plain
text)

 

[Wikked21]

Again, thanks for the interest and help...

Here is some info that you asked for:

Column B & C contains the names, D contains the Enrollment dates, I & J contain the 'credit' columns. An example would be:

D - Enrollment
7/20/04N
7/20/04
7/20/04
6/15/04
9/2/03
6/2/04
2/18/03
9/3/03
10/22/03
10/21/03
3/9/04
12/3/03
8/27/03

J - Credit

N
N
N
N
O
O
Y
O
O
N
O
N
N

"O" denotes not in pool.

I am determining the overall pool / credit and percentage without a problem using the following:
=COUNTIF(I5:I22,"=y")+COUNTIF(I5:I22,"=N") - Pool
=COUNTIF(I5:I22,"=Y") - Credit

What I now want to do is figure out the Pool and Credit for the same data range but only for those with Enrollment dates after a particular date <use 07/01/04> due to changes in reporting.

Again, any help would be appreciated and I hope what I'm looking for is clearer now. I need the actual calculations in order to link these figures into another sheet for month to month & year to year comparisons.

Thanks - B

 

[Frank Kabel]

Hi
try something like
=SUMPRODUCT(--(D5:d22>=DATE(2004,7,1)),--(I5:I22="y"))

 

[Wikked21]

Frank -

Seems to be working fine.... thank you again for the time and patience. It is greatly appreciated!

B