Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Operations with conditional formatting
Can I apply some operations based on cells that have conditional formating?.
I want to count the number of customer that have a specific format I set up. Something like a CountIf or SumIf, but based on format. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Operations with conditional formatting
If the cells are *conditionally formatted* then you can use the same logic
that was used to apply the conditional formatting to then write a formula to do what you want. -- Biff Microsoft Excel MVP "Lily" wrote in message ... Can I apply some operations based on cells that have conditional formating?. I want to count the number of customer that have a specific format I set up. Something like a CountIf or SumIf, but based on format. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Operations with conditional formatting
I think I cannot use the same logic. I will try to explain what I want to do,
hope someone can help me. I have a list of items I want to buy (in a column) from different sellers (in a row). So I am formatting the cheapest price I can find from all the sellers available I have. Now I want to know how much my total purchase would be from each seller. It is like I have a column with all the prices of a particular seller, and some of them have a background yellow because it is the cheapest compared to the other sellers. Now I want to sum all the yellow cells only. Hope it is understandable. Thnaks "T. Valko" wrote: If the cells are *conditionally formatted* then you can use the same logic that was used to apply the conditional formatting to then write a formula to do what you want. -- Biff Microsoft Excel MVP "Lily" wrote in message ... Can I apply some operations based on cells that have conditional formating?. I want to count the number of customer that have a specific format I set up. Something like a CountIf or SumIf, but based on format. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Operations with conditional formatting
So, you want the total of all the min prices per row?
Assume: B1:E1 = sellers A2:A6 = items B2:F6 = prices =SUMPRODUCT(SUBTOTAL(5,OFFSET(B2:F6,ROW(B2:F6)-ROW(B2),,1))) For example: 10...12...10...15...17 17...20...14...22...13 The result would be 23, 10+13. -- Biff Microsoft Excel MVP "Lily" wrote in message ... I think I cannot use the same logic. I will try to explain what I want to do, hope someone can help me. I have a list of items I want to buy (in a column) from different sellers (in a row). So I am formatting the cheapest price I can find from all the sellers available I have. Now I want to know how much my total purchase would be from each seller. It is like I have a column with all the prices of a particular seller, and some of them have a background yellow because it is the cheapest compared to the other sellers. Now I want to sum all the yellow cells only. Hope it is understandable. Thnaks "T. Valko" wrote: If the cells are *conditionally formatted* then you can use the same logic that was used to apply the conditional formatting to then write a formula to do what you want. -- Biff Microsoft Excel MVP "Lily" wrote in message ... Can I apply some operations based on cells that have conditional formating?. I want to count the number of customer that have a specific format I set up. Something like a CountIf or SumIf, but based on format. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Operations with conditional formatting
Ooops!
Typo: Assume: B1:E1 = sellers Should be: B1:F1 = sellers -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... So, you want the total of all the min prices per row? Assume: B1:E1 = sellers A2:A6 = items B2:F6 = prices =SUMPRODUCT(SUBTOTAL(5,OFFSET(B2:F6,ROW(B2:F6)-ROW(B2),,1))) For example: 10...12...10...15...17 17...20...14...22...13 The result would be 23, 10+13. -- Biff Microsoft Excel MVP "Lily" wrote in message ... I think I cannot use the same logic. I will try to explain what I want to do, hope someone can help me. I have a list of items I want to buy (in a column) from different sellers (in a row). So I am formatting the cheapest price I can find from all the sellers available I have. Now I want to know how much my total purchase would be from each seller. It is like I have a column with all the prices of a particular seller, and some of them have a background yellow because it is the cheapest compared to the other sellers. Now I want to sum all the yellow cells only. Hope it is understandable. Thnaks "T. Valko" wrote: If the cells are *conditionally formatted* then you can use the same logic that was used to apply the conditional formatting to then write a formula to do what you want. -- Biff Microsoft Excel MVP "Lily" wrote in message ... Can I apply some operations based on cells that have conditional formating?. I want to count the number of customer that have a specific format I set up. Something like a CountIf or SumIf, but based on format. Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Operations with conditional formatting
Not really, I want the total of all min prices by column actually.
I compared the prices for one item among all my sellers, and chose the one is selling the item for the lowest price. And after that I want to add up all the items I will buy from each seller, so it would be by column S1 S2 S3 S4 I1 5 3 9 2* I2 1* 5 3 4 I3 2* 6 7 5 I4 6 3* 4 9 I5 4 1* 3 5 TotPurch 3 4 0 2 The numbers with * are the ones I formatted with "conditional formatting" (minimun by row). Now I want to add them by column, to see how much I will spend by seller (only buying some items from each of them) It is hard to explain, but thanks for trying to help me. lily PS. Also I will need to count how many items I will buy from each seller,but I assume that if I can sum them up, I will be able to count them up, right?? "T. Valko" wrote: So, you want the total of all the min prices per row? Assume: B1:E1 = sellers A2:A6 = items B2:F6 = prices =SUMPRODUCT(SUBTOTAL(5,OFFSET(B2:F6,ROW(B2:F6)-ROW(B2),,1))) For example: 10...12...10...15...17 17...20...14...22...13 The result would be 23, 10+13. -- Biff Microsoft Excel MVP "Lily" wrote in message ... I think I cannot use the same logic. I will try to explain what I want to do, hope someone can help me. I have a list of items I want to buy (in a column) from different sellers (in a row). So I am formatting the cheapest price I can find from all the sellers available I have. Now I want to know how much my total purchase would be from each seller. It is like I have a column with all the prices of a particular seller, and some of them have a background yellow because it is the cheapest compared to the other sellers. Now I want to sum all the yellow cells only. Hope it is understandable. Thnaks "T. Valko" wrote: If the cells are *conditionally formatted* then you can use the same logic that was used to apply the conditional formatting to then write a formula to do what you want. -- Biff Microsoft Excel MVP "Lily" wrote in message ... Can I apply some operations based on cells that have conditional formating?. I want to count the number of customer that have a specific format I set up. Something like a CountIf or SumIf, but based on format. Thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Operations with conditional formatting
Ok, use a helper column...
=MIN(B2:F2) Copy down as needed. Assume that helper range is G2:G6 Then, to get the totals use something like this: =SUMPRODUCT(--(B2:B6=$G2:$G6),B2:B6) Copy across as needed. For the count: =SUMPRODUCT(--(B2:B6=$G2:$G6)) Copy across as needed. -- Biff Microsoft Excel MVP "Lily" wrote in message ... Not really, I want the total of all min prices by column actually. I compared the prices for one item among all my sellers, and chose the one is selling the item for the lowest price. And after that I want to add up all the items I will buy from each seller, so it would be by column S1 S2 S3 S4 I1 5 3 9 2* I2 1* 5 3 4 I3 2* 6 7 5 I4 6 3* 4 9 I5 4 1* 3 5 TotPurch 3 4 0 2 The numbers with * are the ones I formatted with "conditional formatting" (minimun by row). Now I want to add them by column, to see how much I will spend by seller (only buying some items from each of them) It is hard to explain, but thanks for trying to help me. lily PS. Also I will need to count how many items I will buy from each seller,but I assume that if I can sum them up, I will be able to count them up, right?? "T. Valko" wrote: So, you want the total of all the min prices per row? Assume: B1:E1 = sellers A2:A6 = items B2:F6 = prices =SUMPRODUCT(SUBTOTAL(5,OFFSET(B2:F6,ROW(B2:F6)-ROW(B2),,1))) For example: 10...12...10...15...17 17...20...14...22...13 The result would be 23, 10+13. -- Biff Microsoft Excel MVP "Lily" wrote in message ... I think I cannot use the same logic. I will try to explain what I want to do, hope someone can help me. I have a list of items I want to buy (in a column) from different sellers (in a row). So I am formatting the cheapest price I can find from all the sellers available I have. Now I want to know how much my total purchase would be from each seller. It is like I have a column with all the prices of a particular seller, and some of them have a background yellow because it is the cheapest compared to the other sellers. Now I want to sum all the yellow cells only. Hope it is understandable. Thnaks "T. Valko" wrote: If the cells are *conditionally formatted* then you can use the same logic that was used to apply the conditional formatting to then write a formula to do what you want. -- Biff Microsoft Excel MVP "Lily" wrote in message ... Can I apply some operations based on cells that have conditional formating?. I want to count the number of customer that have a specific format I set up. Something like a CountIf or SumIf, but based on format. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to concatenate operations | Excel Worksheet Functions | |||
Excel Iterations? Conditional Operations? | Excel Discussion (Misc queries) | |||
Excel Iterations? Conditional Operations? | Excel Worksheet Functions | |||
OWC operations in Excel | Excel Discussion (Misc queries) | |||
Intersect operations | Excel Worksheet Functions |