Highlight values that have triggered an IF / SUMIFS statement

[ajnin]

Hello,

Is there a way to highlight values that have been used in a formula

Example formula
=(SUMIFS(All!$B:$B,All!$A:$A,">=1/1/2018",All!$A:$A,"<=31/1/2018",All!$C:$C,"shop1")+(SUMIFS(All!$B:$B,All!$A:$A,">=1/1/2018",All!$A:$A,"<=31/1/2018",All!$C:$C,"shop2")))*-1

My data looks like this

I would like to have all instances of shop1 and shop2 highlighted. As they are in the above formula. i would need to do this for a bunch of formulas all looking for different values.

Or if doing it in reverse is more easy, that would also work. Or any other way I can easy identify what in my data has not been captured by the if statements.

Thanks

 

[tyija1995]

Re: How to Highlight values that have triggered an IF / SUMIFS statement

Hey, you could use Conditional Formatting to do this.

You'll have to make a rule for each SUMIFS statement I believe but it is possible. For example to highlight all of 3 columns that hold true under the first SUMIFS statement (shop1) then use this CF formula:
=AND($A1>=43101,$A1<=43131,$C1="shop1")
Here 43101 is the serial number of 01/01/2018 and 43131 the serial number of 31/01/2018.

Then make sure this applies to your A:C range that the data is in (=$A$1:$C$#) # being the last row

EDIT: If you want the dates more dynamic than serial number then you could use DATE(2018,1,1) and DATE(2018,31,1) instead of 43101 & 43131.

 

[ajnin]

Re: How to Highlight values that have triggered an IF / SUMIFS statement

Thanks for the advise. I will give this a go. Also thanks for the DATE(2018,1,1) tip

Hey, you could use Conditional Formatting to do this.

You'll have to make a rule for each SUMIFS statement I believe but it is possible. For example to highlight all of 3 columns that hold true under the first SUMIFS statement (shop1) then use this CF formula:
=AND($A1>=43101,$A1<=43131,$C1="shop1")
Here 43101 is the serial number of 01/01/2018 and 43131 the serial number of 31/01/2018.

Then make sure this applies to your A:C range that the data is in (=$A$1:$C$#) # being the last row

EDIT: If you want the dates more dynamic than serial number then you could use DATE(2018,1,1) and DATE(2018,31,1) instead of 43101 & 43131.