IF statement dilemma

[JohnE]

I am trying to have the following formula apply criteria
to count the number of Medical Only. I am putting this
into a a Summary sheet. The other sheet is PaceClaims.
The A6 and B6 of Summary have the dates listed
corresponding with the dates on PaceClaims in columns N
and O. I need to know how many Medical Only fall between
the 2 dates. This is not working. I've also been trying
it with ctl+shift+enter.

=IF(PaceClaims!N2:N1000>=A6,AND(PaceClaims!
O2:O1000<B6),COUNT(PaceClaims!G2:G1000="Medical Only",0,1))

Can someone figure why this is not working correctly. I
doesn't give me any error messages.
*** John

 

[Frank Kabel]

Hi
use:
=SUMPRODUCT(--(PaceClaims!N2:N1000>=A6),--(PaceClaims!O2:O1000<B6),--(P
aceClaims!G2:G1000="Medical Only"))

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[JohnE]

Frank, I copied and pasted the info in but it give me 0
both ways (w/o ctl+shift+enter). I rechecked and the
reference to the ranges and cells is correct.
Any other suggestions?
*** John

 

[RagDyeR]

Try this:

=SUMPRODUCT((PaceClaims!N2:N100>=A6)*(PaceClaims!O2:O100<B6)*(PaceClaims!G2:
G100="Medical Only"))
--

HTH,

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


I am trying to have the following formula apply criteria
to count the number of Medical Only. I am putting this
into a a Summary sheet. The other sheet is PaceClaims.
The A6 and B6 of Summary have the dates listed
corresponding with the dates on PaceClaims in columns N
and O. I need to know how many Medical Only fall between
the 2 dates. This is not working. I've also been trying
it with ctl+shift+enter.

=IF(PaceClaims!N2:N1000>=A6,AND(PaceClaims!
O2:O1000<B6),COUNT(PaceClaims!G2:G1000="Medical Only",0,1))

Can someone figure why this is not working correctly. I
doesn't give me any error messages.
*** John

 

[JohnE]

RD, I copied and pasted yours in and it also gives me zero
both ways. I know there are some there as I can see the
first 6 on the list are ones that fit the parameters.
Any other thoughts?
*** John

 

[RagDyeR]

Frank's formula and mine are identical, even though they might look a little
different.

I know that mine works with the info that you posted, so that means that his
works also!

Which means that we test our suggestions with *keyed* in data.
If your results are different, could some of your data be the results of
some formulas you're using to populate your data list, or are you perhaps
importing data, which could bring up formatting issues.

For example, does "Medical Only" have a space in your data?
--

Regards,

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

Frank, I copied and pasted the info in but it give me 0
both ways (w/o ctl+shift+enter). I rechecked and the
reference to the ranges and cells is correct.
Any other suggestions?
*** John

 

[JohnE]

I rechecked and the PaceClaims N and O columns are
formatted dates. The dates in Summary A6 and B6 are dates
formated the same. The dates are 8/1/2001 and 8/1/2002.
The PaceClaims G column has the Medical Only in it just
the way it shows in the formula. There is also Lost Time
in the column as well as Incident Only. I tried those and
it still came up zero. I did a manual count and there are
144 Medical Only within those dates. But I'm getting a
zero in the cell (D6). I even retyped in Medical Only
into the first 2 and I still got zero.
I can't explain it. Any further thoughts>
*** John

 

[Bob Phillips]

Just to verify, try

=SUMPRODUCT(--(PaceClaims!N2:N1000>=A6),--(PaceClaims!O2:O1000<B6),--(TRIM(P
aceClaims!G2:G1000)="Medical Only"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[JohnE]

Bob, I copied and pasted yours and it worked. Thanks.
And also thanks to Frank and RD.
*** John