Formula for the subtotal

[evlai]

i need to know how to calculate of the subtotal for below:

A B C D E F
Year Quater Week Department Agent Action
2005 1 1 Sales Fanny No
2005 1 1 Sales Fanny Yes
2005 1 1 Sales Fanny TBA
2005 1 1 Sales Fanny No
2005 1 1 Sales Fanny No

Subtotal for the Agent's action.
For example
Fanny - No : 3, Yes = 1...etc

Your help is much appreciated. Thanks

 

[Don Guillett]

You might like using COUNTIF

 

[Roger Govier]

Hi

One way
=SUMPRODUCT(--($E2:$E100="Fanny"),--($F$2:$F$200="Yes"))
will count the Yes answers. Change size of ranges to suit.

Better still, in another part of the sheet say columns K to M.
In L1 enter Yes, in M1 enter No, in K2 enter Fanny then in cell L2
=SUMPRODUCT(--($E2:$E100=$K2),--($F$2:$F$200=L$1))
copy across to M2
If you enter more agents names down column K, then just copy l2:M2 down
the page.

If you need to bring in Week number as well, add ,--($C$2:$C$100=1) (or
= cell containing week number required) before the last bracket in the
formula above.
=SUMPRODUCT(--($E2:$E100=$K2),--($F$2:$F$200=L$1),--($C$2:$C$100=1))

 

[Tom Ogilvy]

Assuming you have more than Fanny as an agent, you might look at using a
pivot table to give you a summary in one step.

http://www.contextures.com/tiptech.html
Debra Dalgleish's site - look under P and Pivot Table