Two Formulas, and a problem with both.....................

[Dan the Man]

I am attempting to look at how many individuals during a specific time frame
(Column M) were admitted to our Level 2 program (Column J). The formula I
have below does not seem to be working, as I end up with a "0" outcome, and I
know that I have 4 people who were admitted to Level 2. Thus, I suspect there
is something wrong with the formula I have written below. Any help would be
appreciated:

=SUMPRODUCT(--('ASAP Database'!$M$4:$M$5000>=DATE(2008,1,1)),--('ASAP
Database'!$M$4:$M$5000<=DATE(2008,12,31))*('ASAP
Database'!$J$4:$J$5000="2"))*(ISNUMBER(('ASAP Database'!$J$4:$J$5000)))

The second thing I want to do (in a separate formula), is to look at the
income for this program. The fee is $300.00, so I assume I need to add "*300)
somewhere to achieve that outcome. I couldn't seem to get that either:

=SUMPRODUCT(--('ASAP Database'!$M$4:$M$5000>=DATE(2008,1,1)),--('ASAP
Database'!$M$4:$M$5000<=DATE(2008,12,31))*('ASAP
Database'!$J$4:$J$5000="2"))*300,(ISNUMBER(('ASAP Database'!$J$4:$J$5000)))

 

[Bob Umlas]

Take the quotes off the "2" (unless it's really a text value), and you also
don't need the ISNUMBER (also, change "*to ""):
=SUMPRODUCT(--('ASAP Database'!$M$4:$M$5000>=DATE(2008,1,1)),--('ASAP
Database'!$M$4:$M$5000<=DATE(2008,12,31)),('ASAP Database'!$J$4:$J$5000=2))
Q#2 -- not sure what you want, but looks more like you should multiply the
same formula above by 300:
=300*SUMPRODUCT(--('ASAP Database'!$M$4:$M$5000>=DATE(2008,1,1)),--('ASAP
Database'!$M$4:$M$5000<=DATE(2008,12,31)),('ASAP Database'!$J$4:$J$5000=2))

 

[RagDyeR]

Try this:

To count:
=SUMPRODUCT((YEAR(M4:M5000)=2008)*(J4:J5000=2))

To total:

=SUMPRODUCT((YEAR(M4:M5000)=2008)*(J4:J5000=2))*300

Add your path.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I am attempting to look at how many individuals during a specific time frame
(Column M) were admitted to our Level 2 program (Column J). The formula I
have below does not seem to be working, as I end up with a "0" outcome, and
I
know that I have 4 people who were admitted to Level 2. Thus, I suspect
there
is something wrong with the formula I have written below. Any help would be
appreciated:

=SUMPRODUCT(--('ASAP Database'!$M$4:$M$5000>=DATE(2008,1,1)),--('ASAP
Database'!$M$4:$M$5000<=DATE(2008,12,31))*('ASAP
Database'!$J$4:$J$5000="2"))*(ISNUMBER(('ASAP Database'!$J$4:$J$5000)))

The second thing I want to do (in a separate formula), is to look at the
income for this program. The fee is $300.00, so I assume I need to add
"*300)
somewhere to achieve that outcome. I couldn't seem to get that either:

=SUMPRODUCT(--('ASAP Database'!$M$4:$M$5000>=DATE(2008,1,1)),--('ASAP
Database'!$M$4:$M$5000<=DATE(2008,12,31))*('ASAP
Database'!$J$4:$J$5000="2"))*300,(ISNUMBER(('ASAP Database'!$J$4:$J$5000)))

 

[Dan the Man]

Thanks guys!