Re: Operations with conditional formatting
- From: "T. Valko" <biffinpitt@xxxxxxxxxxx>
- Date: Tue, 13 Jan 2009 17:45:57 -0500
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" <Lily@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B7A54E7A-C202-4923-9C88-E4B36521ECA8@xxxxxxxxxxxxxxxx
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" <Lily@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:17C5CCD2-E020-4F9D-AA43-8CC8BA602BF0@xxxxxxxxxxxxxxxx
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" <Lily@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E736A28A-D96D-4E52-B110-109903B8A713@xxxxxxxxxxxxxxxx
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
.
- References:
- Operations with conditional formatting
- From: Lily
- Re: Operations with conditional formatting
- From: T. Valko
- Re: Operations with conditional formatting
- From: Lily
- Re: Operations with conditional formatting
- From: T. Valko
- Re: Operations with conditional formatting
- From: Lily
- Operations with conditional formatting
- Prev by Date: Re: Finding the number of times a word is used in a range of cells
- Next by Date: RE: Can I round prices to end in .95?
- Previous by thread: Re: Operations with conditional formatting
- Next by thread: Expiration Date countdown
- Index(es):
Relevant Pages
|
