# Sumifs - cells that did not match -- conditional format --Lookup, search, partial match, multiple criteria, lists, duplicate,

#### shakethingsup

##### Board Regular
Hello - I spent the past hour or so searching google and the forums. I can't find exactly what I'm looking for and that's probably because I don't know exactly what I'm looking for.

My real goal is to find and highlight cells that did not get summed up. I.e. if I have a sumifs formula with a dynamic range using indirect, I then want to know, which cells did not sum up and highlight those.

ABCDEFGH
1list 1 - "year" list 2 - "nature"raw data column 1 - "year" - raw data column 2 - "nature"Raw data column 3 - valueHelper column 1helper column 2
22013Cost plus2012Cost plus margin10This should be highlighted as it didn't meet the criteria
32014Cost plus2013Cost plus margin with credit20Included in sum=iferror(lookup(1e+100,search(year,rawdatacolumn1),year),"")
42015Cost exp2014Margin included cost plus pricing30Included in sum
52014Cost plus nothing20Included in sum
2015This was a cost exp item40Included in sum

1. I use a sumifs formula, dynamic range, and partial/wildcard search
-the user has entered in 3 year criteria and 3 nature criteria
-the excel file will search for those combinations and sum

2. The cells (multiple) that don't sum up - how do I highlight those? - these are exceptions - I need a way of highlighting exceptions

I got some ideas from here:

So I tried breaking this down into steps and creating helper columns to figure out the logic but just couldn't get it. If i can get Trues, falses for all my criteria, I can then create a conditional format if all items are true.

The user at their discretion can change list 1 and list 2 - i.e. these are the search criteria to search any data that is plugged into rawdata column 1, 2 and 3.

did this make sense?

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Fluff

##### MrExcel MVP, Moderator
Can't remember if this works in 2013 or not, but try
+Fluff v2.xlsm
ABCDE
1list 1 - "year"list 2 - "nature"raw data column 1 - "year" -raw data column 2 - "nature"Raw data column 3 - value
22013Cost plus2012Cost plus margin10
32014Cost plus2013Cost plus margin with credit20
42015Cost exp2014Margin included cost plus pricing30
52014Cost plus nothing20
62015This was a cost exp item40
7
Work
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2:E6Expression=SUM(COUNTIFS(\$C2,\$A\$2:\$A\$4,\$D2,"*"&\$B\$2:\$B\$4&"*"))=0textNO

#### shakethingsup

##### Board Regular
Worked!!!! wow...

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks fort he feedback.

Replies
3
Views
356
Replies
12
Views
177
Replies
5
Views
304
Replies
7
Views
159
Replies
9
Views
105