COUNTIF: unique names and values accross several columns.

[SW]

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]

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)

 

[ryguy7272]

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

http://www.dailydoseofexcel.com/archives/2004/11/26/creating-a-simple-pivot-table/


Regards,
Ryan---