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

A

aqalna

New Member
Joined
Oct 12, 2016
Messages
8
<article>
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 types
school car food Date
row 1Location1 150.00 500.00 200.004/9/2016
row 2Location2 900.00 450.00 325.004/9/2016
row 3Location3 436.00 543.00 764.004/9/2016
row 4Location4 8,565.00 2,342.00 4,635.004/9/2016
row 5Location5 967.00 876.00 65.004/9/2016
row 6Location6 8,565.00 2,342.00 123.004/9/2016
row 7Location7 904,345.00 8,765.00 6,546.004/9/2016

<colgroup><col><col><col span="3"><col></colgroup><tbody>
</tbody>

row 8Location3 234.00 342.00 657.004/16/2016
row 9Location1 554.00 8,797.00 23,423.004/16/2016
row 10Location6 234.00 34,576.00 75.004/16/2016
row 11Location4 967.00 6,456.00 655,464.004/16/2016
row 12Location2 905.00 873.00 1,232.004/16/2016
row 13Location5 2,347.00 345.00 4,546.004/16/2016
row 14Location8
 5,000.00 4,000.00 2,000.004/16/2016

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

row 15Variance Date4/9/20164/16/2016



Column A

Column B
 Column C
 Colum D
 Column E
row 16Location1404.00
8,297.0023,223.00
row 17Location25.00423.00907.00
row 18Location3-202.00-201.00-107.00
row 19Location4-7,598.00
4,114.00650,829.00
row 20Location51,380.00-531.004,481.00
row 21Location6-8,331.0032,234.00-48.00
row 22Location7-904,345.00-8,765.00-6,546.00
row 23Location85,000.004,000.002,000.00

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>


​
</article>


​

<tbody>
</tbody>
 
Sort by date Sort by votes

Excel 2011 32 bit
ABCDE
1Location11505002004/9/16
2Location29004503254/9/16
3Location3436.00543.007644/9/16
4Location48,565.002,342.004,635.004/9/16
5Location5967.00876.00654/9/16
6Location68,565.002,342.001234/9/16
7Location7904,345.008,765.006,546.004/9/16
8Location32343426574/16/16
9Location15548,797.0023,423.004/16/16
10Location623434,576.00754/16/16
11Location49676,456.00655,464.004/16/16
12Location29058731,232.004/16/16
13Location52,347.003454,546.004/16/16
14Location85,000.004,000.002,000.004/16/16
15
16Location14048,297.0023,223.00
17Location25423907
18Location3-202-201-107
19Location4-7,598.004,114.00650,829.00
20Location51,380.00-5314,481.00
21Location6-8,331.0032,234.00-48
22Location7-904,345.00-8,765.00-6,546.00
23Location85,000.004,000.002,000.00
Sheet2
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
I should've organized my data that way...would've been easier to.understand!

Now you see in column A: i want to develop a formula for rows 16 to 23 under column A
Based on column A again but from rows 1 to 14 under column A


dachickenMind said:
Above is what I have in my table
Click to expand...
 
Upvote 0
Here is the formulas I put in the cells: Please ignore the second table.


Excel 2011 32 bit
ABCDE
1Location115050020042469
2Location290045032542469
3Location343654376442469
4Location485652342463542469
5Location59678766542469
6Location68565234212342469
7Location79043458765654642469
8Location323434265742476
9Location155487972342342476
10Location6234345767542476
11Location4967645665546442476
12Location2905873123242476
13Location52347345454642476
14Location850004000200042476
15
16Location1=SUMIF($A$1:$A$14,A16,$B$1:$B$14)=SUMIF($A$1:$A$14,A16,$C$1:$C$14)=SUMIF($A$1:$A$14,A16,$D$1:$D$14)
17Location2=SUMIF($A$1:$A$14,A17,$B$1:$B$14)=SUMIF($A$1:$A$14,A17,$C$1:$C$14)=SUMIF($A$1:$A$14,A17,$D$1:$D$14)
18Location3=SUMIF($A$1:$A$14,A18,$B$1:$B$14)=SUMIF($A$1:$A$14,A18,$C$1:$C$14)=SUMIF($A$1:$A$14,A18,$D$1:$D$14)
19Location4=SUMIF($A$1:$A$14,A19,$B$1:$B$14)=SUMIF($A$1:$A$14,A19,$C$1:$C$14)=SUMIF($A$1:$A$14,A19,$D$1:$D$14)
20Location5=SUMIF($A$1:$A$14,A20,$B$1:$B$14)=SUMIF($A$1:$A$14,A20,$C$1:$C$14)=SUMIF($A$1:$A$14,A20,$D$1:$D$14)
21Location6=SUMIF($A$1:$A$14,A21,$B$1:$B$14)=SUMIF($A$1:$A$14,A21,$C$1:$C$14)=SUMIF($A$1:$A$14,A21,$D$1:$D$14)
22Location7=SUMIF($A$1:$A$14,A22,$B$1:$B$14)=SUMIF($A$1:$A$14,A22,$C$1:$C$14)=SUMIF($A$1:$A$14,A22,$D$1:$D$14)
23Location8=SUMIF($A$1:$A$14,A23,$B$1:$B$14)=SUMIF($A$1:$A$14,A23,$C$1:$C$14)=SUMIF($A$1:$A$14,A23,$D$1:$D$14)

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2​

Worksheet Formulas
CellFormula
B16=SUMIF($A$1:$A$14,A16,$B$1:$B$14)
C16=SUMIF($A$1:$A$14,A16,$C$1:$C$14)
D16=SUMIF($A$1:$A$14,A16,$D$1:$D$14)
B17=SUMIF($A$1:$A$14,A17,$B$1:$B$14)
C17=SUMIF($A$1:$A$14,A17,$C$1:$C$14)
D17=SUMIF($A$1:$A$14,A17,$D$1:$D$14)
B18=SUMIF($A$1:$A$14,A18,$B$1:$B$14)
C18=SUMIF($A$1:$A$14,A18,$C$1:$C$14)
D18=SUMIF($A$1:$A$14,A18,$D$1:$D$14)
B19=SUMIF($A$1:$A$14,A19,$B$1:$B$14)
C19=SUMIF($A$1:$A$14,A19,$C$1:$C$14)
D19=SUMIF($A$1:$A$14,A19,$D$1:$D$14)
B20=SUMIF($A$1:$A$14,A20,$B$1:$B$14)
C20=SUMIF($A$1:$A$14,A20,$C$1:$C$14)
D20=SUMIF($A$1:$A$14,A20,$D$1:$D$14)
B21=SUMIF($A$1:$A$14,A21,$B$1:$B$14)
C21=SUMIF($A$1:$A$14,A21,$C$1:$C$14)
D21=SUMIF($A$1:$A$14,A21,$D$1:$D$14)
B22=SUMIF($A$1:$A$14,A22,$B$1:$B$14)
C22=SUMIF($A$1:$A$14,A22,$C$1:$C$14)
D22=SUMIF($A$1:$A$14,A22,$D$1:$D$14)
B23=SUMIF($A$1:$A$14,A23,$B$1:$B$14)
C23=SUMIF($A$1:$A$14,A23,$C$1:$C$14)
D23=SUMIF($A$1:$A$14,A23,$D$1:$D$14)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:
Upvote 0
I am not sure if your understanding my question but I am not looking for formulas in columns "B to D" instead I am focused in column "A" where I care in creating a formula under column "A" but for rows 16 to 23 that will be based on the data above in rows (1 to 14) under column "A" again.

I hope that makes it clearer in what I am trying to find.

dachickenMind said:
Here is the formulas I put in the cells: Please ignore the second table.


Excel 2011 32 bit
ABCDE
1Location115050020042469
2Location290045032542469
3Location343654376442469
4Location485652342463542469
5Location59678766542469
6Location68565234212342469
7Location79043458765654642469
8Location323434265742476
9Location155487972342342476
10Location6234345767542476
11Location4967645665546442476
12Location2905873123242476
13Location52347345454642476
14Location850004000200042476
15
16Location1=SUMIF($A$1:$A$14,A16,$B$1:$B$14)=SUMIF($A$1:$A$14,A16,$C$1:$C$14)=SUMIF($A$1:$A$14,A16,$D$1:$D$14)
17Location2=SUMIF($A$1:$A$14,A17,$B$1:$B$14)=SUMIF($A$1:$A$14,A17,$C$1:$C$14)=SUMIF($A$1:$A$14,A17,$D$1:$D$14)
18Location3=SUMIF($A$1:$A$14,A18,$B$1:$B$14)=SUMIF($A$1:$A$14,A18,$C$1:$C$14)=SUMIF($A$1:$A$14,A18,$D$1:$D$14)
19Location4=SUMIF($A$1:$A$14,A19,$B$1:$B$14)=SUMIF($A$1:$A$14,A19,$C$1:$C$14)=SUMIF($A$1:$A$14,A19,$D$1:$D$14)
20Location5=SUMIF($A$1:$A$14,A20,$B$1:$B$14)=SUMIF($A$1:$A$14,A20,$C$1:$C$14)=SUMIF($A$1:$A$14,A20,$D$1:$D$14)
21Location6=SUMIF($A$1:$A$14,A21,$B$1:$B$14)=SUMIF($A$1:$A$14,A21,$C$1:$C$14)=SUMIF($A$1:$A$14,A21,$D$1:$D$14)
22Location7=SUMIF($A$1:$A$14,A22,$B$1:$B$14)=SUMIF($A$1:$A$14,A22,$C$1:$C$14)=SUMIF($A$1:$A$14,A22,$D$1:$D$14)
23Location8=SUMIF($A$1:$A$14,A23,$B$1:$B$14)=SUMIF($A$1:$A$14,A23,$C$1:$C$14)=SUMIF($A$1:$A$14,A23,$D$1:$D$14)

<tbody>
</tbody>
Sheet2​

Worksheet Formulas
CellFormula
B16=SUMIF($A$1:$A$14,A16,$B$1:$B$14)
C16=SUMIF($A$1:$A$14,A16,$C$1:$C$14)
D16=SUMIF($A$1:$A$14,A16,$D$1:$D$14)
B17=SUMIF($A$1:$A$14,A17,$B$1:$B$14)
C17=SUMIF($A$1:$A$14,A17,$C$1:$C$14)
D17=SUMIF($A$1:$A$14,A17,$D$1:$D$14)
B18=SUMIF($A$1:$A$14,A18,$B$1:$B$14)
C18=SUMIF($A$1:$A$14,A18,$C$1:$C$14)
D18=SUMIF($A$1:$A$14,A18,$D$1:$D$14)
B19=SUMIF($A$1:$A$14,A19,$B$1:$B$14)
C19=SUMIF($A$1:$A$14,A19,$C$1:$C$14)
D19=SUMIF($A$1:$A$14,A19,$D$1:$D$14)
B20=SUMIF($A$1:$A$14,A20,$B$1:$B$14)
C20=SUMIF($A$1:$A$14,A20,$C$1:$C$14)
D20=SUMIF($A$1:$A$14,A20,$D$1:$D$14)
B21=SUMIF($A$1:$A$14,A21,$B$1:$B$14)
C21=SUMIF($A$1:$A$14,A21,$C$1:$C$14)
D21=SUMIF($A$1:$A$14,A21,$D$1:$D$14)
B22=SUMIF($A$1:$A$14,A22,$B$1:$B$14)
C22=SUMIF($A$1:$A$14,A22,$C$1:$C$14)
D22=SUMIF($A$1:$A$14,A22,$D$1:$D$14)
B23=SUMIF($A$1:$A$14,A23,$B$1:$B$14)
C23=SUMIF($A$1:$A$14,A23,$C$1:$C$14)
D23=SUMIF($A$1:$A$14,A23,$D$1:$D$14)

<tbody>
</tbody>

<tbody>
</tbody>
Click to expand...
 
Upvote 0
Hi,
Use the below formula to build unique location in Column A. You need a array formula to build unique locations.

Excel 2011 32 bit
A
1Location
2Location1
3Location2
4Location1
5Location4
6Location5
7Location6
8Location7
9Location3
10Location1
11Location6
12Location4
13Location2
14Location5
15Location8
16
17MyUniqueLocation
18=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A17,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A17,$A$2:$A$15),0)))
19=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A18,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A18,$A$2:$A$15),0)))
20=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A19,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A19,$A$2:$A$15),0)))
21=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A20,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A20,$A$2:$A$15),0)))
22=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A21,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A21,$A$2:$A$15),0)))
23=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A22,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A22,$A$2:$A$15),0)))
24=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A23,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A23,$A$2:$A$15),0)))
25=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A24,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A24,$A$2:$A$15),0)))
26=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A25,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A25,$A$2:$A$15),0)))
27=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A26,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A26,$A$2:$A$15),0)))
28=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A27,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A27,$A$2:$A$15),0)))
29

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>
Myunique​

Array Formulas
CellFormula
A18{=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A17,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A17,$A$2:$A$15),0)))}
A19{=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A18,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A18,$A$2:$A$15),0)))}
A20{=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A19,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A19,$A$2:$A$15),0)))}
A21{=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A20,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A20,$A$2:$A$15),0)))}
A22{=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A21,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A21,$A$2:$A$15),0)))}
A23{=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A22,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A22,$A$2:$A$15),0)))}
A24{=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A23,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A23,$A$2:$A$15),0)))}
A25{=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A24,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A24,$A$2:$A$15),0)))}
A26{=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A25,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A25,$A$2:$A$15),0)))}
A27{=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A26,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A26,$A$2:$A$15),0)))}
A28{=IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A27,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A27,$A$2:$A$15),0)))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Last edited:
Upvote 0
Hi,
Use the above formula to build unique locations in Column A. You need an array formula to build unique locations. First, in A17, you need to input "MyUniqueLocation" as first keyword. You can type in any title, just do not have a same name like your location list. For example, you can not put location1 in the A17. We need this filed to avoid circle reference in the array formula. In A18, Please copy paste =IF(ISERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A17,$A$2:$A$15),0))),"",INDEX($A$2:$A$15,MATCH(0,COUNTIF($A$17:$A17,$A$2:$A$15),0))), Then,Control+Shift+Enter, the curved brackets will show in the formula indicating that is an array formula. Last step, drag down the formula as many row as you want. You got unique location list :)' The second table also has comments to explain how to enter array formula.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Counting a list of wild cards from a range of cells
Replies
3
Views
257
Fluff
Fluff
R
  • Solved
VBA Help - Split Data Worksheet with helper sheet into separate workbooks, preserve all formatting and structure and PW protect sheet
Replies
6
Views
166
John_w
J
H
  • Solved
Reference to ...ListColumns("<name>"").index in Conditional Formatting Formula
Replies
3
Views
518
Hans Troost
H
B
  • Question
Help with Conditional Formatting please!
Replies
8
Views
229
Fluff
Fluff
F
  • Question
VBA: Hide rows based on column A
Replies
8
Views
1K
My Aswer Is This
My Aswer Is This

Forum statistics

Threads
1,216,109
Messages
6,128,883
Members
449,477
Latest member
panjongshing

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top