Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Conditional Formatting and sumif or index or another formula? (please help!)

Posted on 2016-10-12
Medium Priority
Last Modified: 2016-11-11
I am working on a file and still need help for the following:

Is there a way to retrieve the information for "rows 16 to 23" under column "B" from where it populates the data based on "rows 1 to 14" under column "B"

Lastly, I am trying to figure out how can I highlight the "row 22" (Location 7) in yellow since it does not exist anymore from "rows 8 to 14"

As well, similar to "row 23" (location 8) where it is newly added and is not shown in "rows 8 to 14" so I want that to be highlighted in light blue.

Let me know if anyone can teach me in doing that please.

Here is my data set:
                    Column A  Column B             Column C            Column D                 Column E                            

              Areas                  school                      car                          food                              Date
row 1       Location1       150.00                     500.00                     200.00                  4/9/2016
row 2       Location2       900.00                     450.00                     325.00                   4/9/2016
row 3       Location3       436.00                      543.00                      764.00                   4/9/2016
row 4       Location4       8,565.00                2,342.00                      4,635.00                    4/9/2016
row 5       Location5       967.00                        876.00                     65.00                   4/9/2016
row 6       Location6       8,565.00              2,342.00                    123.00                   4/9/2016
row 7       Location7       904,345.00            8,765.00              6,546.00                  4/9/2016

row 8       Location3       234.00                       342.00                     657.00                4/16/2016
row 9       Location1       554.00                       8,797.00             23,423.00       4/16/2016
row 10       Location6       234.00                       34,576.00              75.00               4/16/2016
row 11       Location4       967.00                        6,456.00              655,464.00       4/16/2016
row 12       Location2       905.00                        873.00                        1,232.00       4/16/2016
row 13       Location5       2,347.00                 345.00                      4,546.00       4/16/2016
row 14       Location8       5,000.00                   4,000.00                2,000.00       4/16/2016

row 15       Variance Date       4/9/2016       4/16/2016       

 Column A
               Column B       Column C                            Colum D                       Column E
row 16       Location1       404.00                                      8,297.00                     23,223.00
row 17       Location2       5.00                                         423.00                        907.00
row 18       Location3       -202.00                                      -201.00                         -107.00
row 19       Location4       -7,598.00                                4,114.00                   650,829.00
row 20       Location5       1,380.00                                 -531.00                    4,481.00
row 21       Location6       -8,331.00                                 32,234.00                     -48.00
row 22       Location7       -904,345.00                               -8,765.00                       -6,546.00
row 23       Location8       5,000.00                                  4,000.00                      2,000.00
Question by:aa aa
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

by:Tim Berry
ID: 41841026
LVL 27

Expert Comment

by:Glenn Ray
ID: 41841126
Yes, you will indeed want to use SUMIFS to determine both the variance calculations on the bottom AND highlight cells where any location has no transactions (or zero total transactions) in the latter variance date.

Your data example doesn't include the header labels (Areas, School, Car, Food, Date) in row 1, so all your example references are off by one.  So, I created an example workbook based on your data.

If, given that the balance data exists in rows 2:15, and the variance dates are in cells B17 and C17 (an extra blank row below), the formula for the variance for each location is:


This is copied down and across as needed.

As for the conditional formatting, the above formula has part of the rule needed, you only need to check if it is equal to zero:
EE-ConditionalFormatting-SUMIFS.pngthen apply that to all the cells with the SUMIFS, in the case of this example workbook, B18:D25

See the attached example file.


Author Comment

by:aa aa
ID: 41841302
I just noticed I could attach a file ahh!!

sorry I didn't know we could attach ...would've made both our lives easier lol!

so the grey areas is what I need to populate

also i highlight the areas that I need help on conditional formatting.

Basically In the first set of data (rows 4 to 10) if Location 7 or any locations to be exact does not exist in the second set of data (rows 13 to 19) than highlight it yellow

and if the second set of data (rows 13 to 19) has a new location ie: location 8 , location 9 etc... and is not found in the first set of data (rows 4 to 10) than highlight them blue.

Than in the summary section (rows 32 to 39) have the respective "newly added" or "removed" to its colours

removed = yellow

newly added = blue
LVL 27

Accepted Solution

Glenn Ray earned 2000 total points
ID: 41842902
Fortunately, your use of dynamic ranges helps simplify the conditional formatting needed.  You just check if the location value in column F occurs in the other range.

So, the rule for rows 4:10 is

and for rows 13:19

Since you want the same rule applied in the bottom section, you can extend each rule to also include rows 32:39.

Now, this raises an issue.  Will it ever be possible for a location that is listed in the bottom section be missing from BOTH of the top sections?  

See the attached, modified version of your workbook.

LVL 14

Expert Comment

ID: 41883562
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: Glenn Ray (https:#a41842902)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

Experts-Exchange Cleanup Volunteer

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question