Counting/Summing Arrays

[RFJ]

I have a worksheet of a simple fitness scheme comprising 5 main columns

A Peoples Names
B Their Training day
C Target Fitness
D Actual Fitness
eg

A B C D
John Monday 200 150
Sue Tuesday 300 320
etc


I want to do a count, by training day, of how many people are above/below
their target

Then I want to add up, again by training day, the total shortfalls of those
below target.

I can't get the array formula syntax right. Can SKS help me out.

TIA

Robin

 

[Bob Phillips]

1) =SUMPRODUCT(--(B1:B20="Monday"),--(C1:C20>D120))

2) =SUMPRODUCT(--(B1:B20="Monday"),--(C1:C20>D120),(C1:C20-D120))

 

[RFJ]

Thanks Bob, works just as I want <BG>

I haven't seen the double negative before; for info can you tell me what it
does. ie :

--(B1:B20="Monday")

Tx

Robin

 

[Bob Phillips]

Robin,

It is all explained here http://www.xldynamic.com/source/xld.SUMPRODUCT.html