Subtotal

[anukool]

I have a sheet containing Customer, State, Sales, Outstanding.
- I would like to find the contribution of each Customer against the
total sales for the state in which he/she is located.
- I would like to sub-total for each state only customers who have
contributed to atleast 90% of the respective State's sale.

Enclosing a sample sheet containing the original sheet and the desired
output. At present we have one person doing this manually every week.
This is not desireable as the volume of data is very high and we donot
have a database system to do this.

Any suggestions?


+-------------------------------------------------------------------+
|Filename: sample.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4711 |
+-------------------------------------------------------------------+

 

[Bob Phillips]

I have a sheet containing Customer, State, Sales, Outstanding.
- I would like to find the contribution of each Customer against the
total sales for the state in which he/she is located.

In E3

=SUMPRODUCT(--($A$3:$A$23=$A3),--($B$3:$B$23=$B3),$C$3:$C$23)

- I would like to sub-total for each state only customers who have
contributed to atleast 90% of the respective State's sale.

In F3

=E3>=SUMIF($B$3:$B$23,$B3,$C$3:$C$23)*90%