Formulas on Subtotaled data

[bdanner]

I wanted to see if I could get advice on how I would go about applying a formula to subtotaled data (formula autoadjusts itself to look at the correct range of data in the subtotaled section):

Scenario:

Data is subtotaled by a customer name
Customer has different status of open and closed projects (data element indicates the status- OP for open CL for closed for each project).
I'd like to know the % of closed projects for each customer populated beside the subtotaled data. (I've come up with =(COUNTIF(E2:E20,"CL")/COUNTA(E2:20)) as an example- trying to figure out how to get excel to use this same type of formula for the additional subtotals (for customer) below this.

End result in would look like:

Customer A - 50% compelete
Customer B- 90% complete
....

This would need to be done daily based on new data- just trying to find a way to automate this.

I really apprecaite any advice /assistance/ guidance.

Thanks!!

 

[fairwinds]

Hi,

Try:

=SUMPRODUCT(--(A1:A30="CustomerA"),--(B1:B30="CL"))/COUNTIF(A1:A30,"CustomerA")

 

[bdanner]

Hi Fairwinds-

I think the only problem with including the customer name on the formula is that the customer name changes daily so it would be difficult to include this in the formula (it would take a manual change each day). Do you know of a way for Excel to look at the status column (after having subtotaled for customer name) to populate the % of calls that are showing to be "CL"? (in the blank cell that is geneted below the status dataset). The number of particular projects for a given customer could change each day so I am trying to find a way to make the formula dynamic to look for each change in customer name somehow when calculating the % of projects complete for each customer.

Thanks.

 

[fairwinds]

If you have your names in col A

You could enter

=LOOKUP(2,1/ISNA(MATCH($A$2:$A$100,$D$1:D1,0)),$A$2:$A$100)

in D2 and drag down.

It should give you a unique list in reversed order.

 

[bdanner]

Just tried the suggested formula and I think I may need another approach. I have come up with an example of what my spreadsheet will look like- trying to fill in the cells with ?'s (highlighted) automatically (the formula in C8 is =(COUNTIF(C2:C7,"CL"))/COUNTA(C2:C7) to yeild 50% need to be able to duplicate that formula in C13, etc- the formula in C13 should read =(COUNTIF(C9:C12,"CL"))/COUNTA(C9:C12))). Number of "other customer data" for customer's will change each day adding/ subtracting extra lines. Trying to come up with a way to automatically populate the % of projects in CL status for each customer (and then be able to roll up the subtotal to only display the customer name, count, and % complete:

Book2.xls
	A	B	C	D
1	Other Customer Data	Customer Name	Status
2	A	Customer A	CL
3	B	Customer A	CL
4	C	Customer A	OP
5	F	Customer A	OP
6	G	Customer A	OP
7	H	Customer A	CL
8	Customer A Count	6	50.0%
9	I	Customer B	CL
10	J	Customer B	CL
11	M	Customer B	CL
12	N	Customer B	OP
13	Customer B Count	4	?
14	O	Customer C	OP
15	P	Customer C	OP
16	Q	Customer C	OP
17	X	Customer C	CL
18	Y	Customer C	CL
19	Z	Customer C	CL
20	Customer C Count	6	?
21	AA	Customer D	OP
22	AB	Customer D	CL
23	AC	Customer D	OP
24	Customer D Count	3	?
25	Grand Count	19	?
Sheet1

 

[fairwinds]

You could try:

=IF(ISNUMBER(B2),SUMPRODUCT(--($B$1:B1=B1),--($C$1:C1="CL"))/B2,"")

In D2, drag down

Book1
	A	B	C	D
1	Other Customer Data	Customer Name	Status
2	A	Customer A	CL
3	B	Customer A	CL
4	C	Customer A	OP
5	F	Customer A	OP
6	G	Customer A	OP
7	H	Customer A	CL
8	Customer A Count	6		50%
9	I	Customer B	CL
10	J	Customer B	CL
11	M	Customer B	CL
12	N	Customer B	OP
13	Customer B Count	4		75%
14	O	Customer C	OP
15	P	Customer C	OP
16	Q	Customer C	OP
17	X	Customer C	CL
18	Y	Customer C	CL
19	Z	Customer C	CL
20	Customer C Count	6		50%
21	AA	Customer D	OP
22	AB	Customer D	CL
23	AC	Customer D	OP
24	Customer D Count	3		33%
25	Grand Count	19
Sheet4

 

[bdanner]

Fairwinds- Thank you so much- that is exactly what I needed!!

 

[bdanner]

Fairwinds-

I have one last question on this formula- how would you go about adding in an extra project type code in the calculation

ie: what % of projects are in CL or CN status?

I've tried to modify the formula but it does not seem to work:

=IF(ISNUMBER(D2),SUMPRODUCT(--($D$1:D1=D1),--($E$1:E1="CM"),--($E$1:E1="CN")/D2,""))

Would I need to use the COUNTIF formula?[/b]

 

[fairwinds]

=IF(ISNUMBER(B2),SUMPRODUCT(--($B$1:B1=B1),($C$1:C1="CL")+($C$1:C1="CN"))/B2,"")

 

[bdanner]

That worked- thank you so much for your help!