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

shakethingsup

Board Regular
Joined
May 21, 2017
Messages
50
Office Version
  1. 2013
Platform
  1. Windows
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?
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks fort he feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,255
Messages
5,576,994
Members
412,759
Latest member
Jackuk127
Top