I am trying to get a count of every time each person does not meet goal.
I've spent some time on this and my best attempt is:

COUNT(IF(($A$2:$A$11="XXX")*($D$2:$D$11>C2:C11)*$G2:G11<F2:F11*J2:J11<I2:I11)).
It doesn't work, of course, but I've spent some time creating chaos. Can
anyone help me with this? Below is an example of how the data might look:

A C D F G
I J
Name Goal Parts Made Goal Parts Made Goal Parts Made
XXX 8 7 5 8
20 14
YYY 3 4 7 6
5 6
XXX 9 10 6 5
25 23
ZZZ 5 8 12 10
15 14
YYY 9 5 22 22
18 19
ZZZ 50 45 16 18 24
25

Maybe...

=sumproduct(--($A$2:$A$11="XXX"),
--($D$2:$D$11>$C$2:$C$11),
--($G$2:$G$11<$F$2:$F$11),
--($J$2:$J$11<$I$2:$I$11))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

====

I changed all the addresses to absolute (added $ to each).

And what happens if you use your formula, but use:
=sum(if(....
instead of
=count(if(...
(still array entered)



SW wrote:
>
> I am trying to get a count of every time each person does not meet goal.
> I've spent some time on this and my best attempt is:
>
> COUNT(IF(($A$2:$A$11="XXX")*($D$2:$D$11>C2:C11)*$G2:G11<F2:F11*J2:J11<I2:I11)).
> It doesn't work, of course, but I've spent some time creating chaos. Can
> anyone help me with this? Below is an example of how the data might look:
>
> A C D F G
> I J
> Name Goal Parts Made Goal Parts Made Goal Parts Made
> XXX 8 7 5 8
> 20 14
> YYY 3 4 7 6
> 5 6
> XXX 9 10 6 5
> 25 23
> ZZZ 5 8 12 10
> 15 14
> YYY 9 5 22 22
> 18 19
> ZZZ 50 45 16 18 24
> 25

--

Dave Peterson

A Pivot table may work for you too:
http://peltiertech.com/Excel/Pivots/pivottables.htm

http://www.dailydoseofexcel.com/arch...e-pivot-table/


Regards,
Ryan---

--
RyGuy


"Dave Peterson" wrote:

> Maybe...
>
> =sumproduct(--($A$2:$A$11="XXX"),
> --($D$2:$D$11>$C$2:$C$11),
> --($G$2:$G$11<$F$2:$F$11),
> --($J$2:$J$11<$I$2:$I$11))
>
> Adjust the ranges to match--but you can't use whole columns (except in xl2007).
>
> =sumproduct() likes to work with numbers. The -- stuff changes trues and falses
> to 1's and 0's.
>
> Bob Phillips explains =sumproduct() in much more detail here:
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
>
> And J.E. McGimpsey has some notes at:
> http://mcgimpsey.com/excel/formulae/doubleneg.html
>
> ====
>
> I changed all the addresses to absolute (added $ to each).
>
> And what happens if you use your formula, but use:
> =sum(if(....
> instead of
> =count(if(...
> (still array entered)
>
>
>
> SW wrote:
> >
> > I am trying to get a count of every time each person does not meet goal.
> > I've spent some time on this and my best attempt is:
> >
> > COUNT(IF(($A$2:$A$11="XXX")*($D$2:$D$11>C2:C11)*$G2:G11<F2:F11*J2:J11<I2:I11)).
> > It doesn't work, of course, but I've spent some time creating chaos. Can
> > anyone help me with this? Below is an example of how the data might look:
> >
> > A C D F G
> > I J
> > Name Goal Parts Made Goal Parts Made Goal Parts Made
> > XXX 8 7 5 8
> > 20 14
> > YYY 3 4 7 6
> > 5 6
> > XXX 9 10 6 5
> > 25 23
> > ZZZ 5 8 12 10
> > 15 14
> > YYY 9 5 22 22
> > 18 19
> > ZZZ 50 45 16 18 24
> > 25
>
> --
>
> Dave Peterson
>