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

[shakethingsup]

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.

	A	B	C	D	E	F	G	H
1	list 1 - "year"	list 2 - "nature"	raw data column 1 - "year" -	raw data column 2 - "nature"	Raw data column 3 - value		Helper column 1	helper column 2
2	2013	Cost plus	2012	Cost plus margin	10	This should be highlighted as it didn't meet the criteria
3	2014	Cost plus	2013	Cost plus margin with credit	20	Included in sum	=iferror(lookup(1e+100,search(year,rawdatacolumn1),year),"")
4	2015	Cost exp	2014	Margin included cost plus pricing	30	Included in sum
5			2014	Cost plus nothing	20	Included in sum
			2015	This was a cost exp item	40	Included 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:

Conditional formatting with formulas

Although Excel ships with many conditional formatting "presets", these are limited. A more powerful way to apply conditional formatting is with formulas, because formulas allow you to apply rules that use more sophisticated logic. This article shows 10 examples, including how to highlight rows...

exceljet.net

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?

 

[Fluff]

Can't remember if this works in 2013 or not, but try

+Fluff v2.xlsm
	A	B	C	D	E
1	list 1 - "year"	list 2 - "nature"	raw data column 1 - "year" -	raw data column 2 - "nature"	Raw data column 3 - value
2	2013	Cost plus	2012	Cost plus margin	10
3	2014	Cost plus	2013	Cost plus margin with credit	20
4	2015	Cost exp	2014	Margin included cost plus pricing	30
5			2014	Cost plus nothing	20
6			2015	This was a cost exp item	40
7
Work

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:E6	Expression	=SUM(COUNTIFS($C2,$A$2:$A$4,$D2,"*"&$B$2:$B$4&"*"))=0	text	NO

 

[shakethingsup]

Worked!!!! wow...

 

[Fluff]

You're welcome & thanks fort he feedback.