[Solved] Conditional formatting - special issue

Discuss the spreadsheet application

[Solved] Conditional formatting - special issue

Postby khostomsky » Sat Dec 21, 2019 11:12 am

Hello,
I need a special conditional formatting. The situation is this.
In column "A" I have unique IDs (lets assume names), which can repeat in rows (this is important) but not necessarily. To each unique value belongs a value in a different column "B". I need to highlight a situation when to a specific unique ID in column "A" the SUM of the values in column "B" reach a certain value.

Example:
If to a certain ID the SUM of the values in column "B" reach "10" then should be highlighted the row (or cell).

ID Value
Peter 5
Suzanne 2
Andrew 3
Thomas 12
Peter 6
Mia 9
Donald 10
Boris 4
Last edited by Hagar Delest on Mon Dec 23, 2019 7:34 pm, edited 1 time in total.
Reason: tagged solved.
LibreOffice 5.4.7.2 (x64) on Windows 6.19
khostomsky
 
Posts: 4
Joined: Sat Dec 21, 2019 11:07 am

Re: Conditional formatting - special issue

Postby F3K Total » Sat Dec 21, 2019 12:28 pm

Try this as condition if to use until row 30:
Formula is
Code: Select all   Expand viewCollapse view
SUMIF($A$2:$A$30;$A2;$B$2:$B$30)>=10
R
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 974
Joined: Fri Dec 16, 2011 8:20 pm

Re: Conditional formatting - special issue

Postby khostomsky » Sat Dec 21, 2019 1:12 pm

It did this:

Its npt what I am looking for. Different criteria I need :(
I need highlighted Peter, Thomas and Donald.
Attachments
asd.PNG
asd.PNG (5.35 KiB) Viewed 422 times
LibreOffice 5.4.7.2 (x64) on Windows 6.19
khostomsky
 
Posts: 4
Joined: Sat Dec 21, 2019 11:07 am

Re: Conditional formatting - special issue

Postby Lupp » Sat Dec 21, 2019 3:13 pm

Unfortunately you didn't tell us the "certain value" your example is working with. In addition the highlightings given in the original question and in the image (Always attach files if not the question specifically addresse an issue of the view!) respectively are incompatible.

The explantion is also unclear. Shall we suppose you want to start adding anew as soon as the "certain value" was -at least- reached?

No reasonable answer possible in the given situation. Rework your question and attach a .ods containing a sufficient number of speaking and consistent examples, and the "certain value" for each of them, of course.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2911
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Conditional formatting - special issue

Postby khostomsky » Sat Dec 21, 2019 3:19 pm

Hello,
sorry for the inexactness, I am dealing with this kind of problem every 5 years..

So the idea is that I have clients - column "A".
Every client buy some lollypops during the year. - column "B" (number of lollypops bought)
Each client can buy several times per year - which mean the same client (unique ID) can buy more than once -> 1 client has more rows.

Objective: Highlight the row, when a certain client has bought already at least 10 or more lollypops during the year (reached 10 pieces) :D

Thank you,
K.
Last edited by khostomsky on Sat Dec 21, 2019 4:56 pm, edited 1 time in total.
LibreOffice 5.4.7.2 (x64) on Windows 6.19
khostomsky
 
Posts: 4
Joined: Sat Dec 21, 2019 11:07 am

Re: Conditional formatting - special issue

Postby Lupp » Sat Dec 21, 2019 4:52 pm

(The image still does not match the given values .)

You can use SUMIF() or SUMPRODUCT() in the condition for the CF.
See attached demo.
Attachments
aoo100492_CFwithSumIf_1.ods
(16.41 KiB) Downloaded 14 times
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2911
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Conditional formatting - special issue

Postby khostomsky » Sat Dec 21, 2019 5:38 pm

Yes, that is it. Thank you very much!

Any idea why accidentally once in a time the setting of conditional formatting disappears?

Thanks,
K.
LibreOffice 5.4.7.2 (x64) on Windows 6.19
khostomsky
 
Posts: 4
Joined: Sat Dec 21, 2019 11:07 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 7 guests