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

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

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

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

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

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

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