Operations with conditional formatting

L

Lily

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
 
T

T. Valko

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.
 
L

Lily

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

T. Valko

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.
 
L

Lily

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

T. Valko

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top