Formulas on Subtotaled data

bdanner

New Member
Joined
May 19, 2002
Messages
34
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!!
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

Try:

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

bdanner

New Member
Joined
May 19, 2002
Messages
34
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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

New Member
Joined
May 19, 2002
Messages
34

ADVERTISEMENT

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
ABCD
1Other Customer DataCustomer NameStatus
2ACustomer ACL
3BCustomer ACL
4CCustomer AOP
5FCustomer AOP
6GCustomer AOP
7HCustomer ACL
8Customer A Count650.0%
9ICustomer BCL
10JCustomer BCL
11MCustomer BCL
12NCustomer BOP
13Customer B Count4?
14OCustomer COP
15PCustomer COP
16QCustomer COP
17XCustomer CCL
18YCustomer CCL
19ZCustomer CCL
20Customer C Count6?
21AACustomer DOP
22ABCustomer DCL
23ACCustomer DOP
24Customer D Count3?
25Grand Count19?
Sheet1
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
You could try:

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

In D2, drag down
Book1
ABCD
1Other Customer DataCustomer NameStatus
2ACustomer ACL 
3BCustomer ACL 
4CCustomer AOP 
5FCustomer AOP 
6GCustomer AOP 
7HCustomer ACL 
8Customer A Count650%
9ICustomer BCL 
10JCustomer BCL 
11MCustomer BCL 
12NCustomer BOP 
13Customer B Count475%
14OCustomer COP 
15PCustomer COP 
16QCustomer COP 
17XCustomer CCL 
18YCustomer CCL 
19ZCustomer CCL 
20Customer C Count650%
21AACustomer DOP 
22ABCustomer DCL 
23ACCustomer DOP 
24Customer D Count333%
25Grand Count19
Sheet4
 

bdanner

New Member
Joined
May 19, 2002
Messages
34

ADVERTISEMENT

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

bdanner

New Member
Joined
May 19, 2002
Messages
34
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
=IF(ISNUMBER(B2),SUMPRODUCT(--($B$1:B1=B1),($C$1:C1="CL")+($C$1:C1="CN"))/B2,"")
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top