=COUNTIFS Function w/ Multiple sheets & Ranges + Formatting

[Dubyah]

My function is as follows and is currently working.. but I was wondering if there was an easier way to refer to another sheet (Range) 'Secret List' within a countif function, or another way to do the same thing.

=COUNTIFS(C304,'Secret List'!$B$3)+COUNTIFS(C304,'Secret List'!$B$4)+COUNTIFS(C304,'Secret List'!$B$5)+COUNTIFS(C304,'Secret List'!$B$6)+COUNTIFS(C304,'Secret List'!$B$7)

The current function returns a value of "1" if the cell in column C matches specific cells on sheet 'secret list'.

Essentially I would like to be able to refer to the range on sheet 'secret' list rather than individual cells. Is this possible?

Additionally, I'm having some difficulty adding a conditional format to the rows that contain cells returning '1' from the above function. I would like the rows returning the '1' value to be highlighted.

THanks,
Will

 

[Aladin Akyurek]

Looks like you want:

=COUNTIFS('Secret List'!$B$3:$B$7,C304)

 

[Dubyah]

Wow, interesting.. That does the trick! Any advice on how to conditionally format the rows based on the identifier returned from the above function?

 

[Aladin Akyurek]

Wow, interesting.. That does the trick! Any advice on how to conditionally format the rows based on the identifier returned from the above function?

=COUNTIFS('Secret List'!$B$3:$B$7,C304)

returns an occurrence count of the value C304 houses.

Are you wanting to conditionally format any cell in B3:B7 of Secret List when it is equal to the C304?

 

[Dubyah]

Any cell in not in the secret list.

Sheet one has 900+ entries the secret list just has a few. So in other words conditionally format cells on sheet one to identify entries also on the secret list. Ideally highlight the entire row. Rather than just the single identifying cell.

 

[Aladin Akyurek]

Any cell in not in the secret list.

Sheet one has 900+ entries the secret list just has a few. So in other words conditionally format cells on sheet one to identify entries also on the secret list. Ideally highlight the entire row. Rather than just the single identifying cell.

Select the sheet one entries (which is assumed to start in A2 in the example below) and run the following formula:

=MATCH(A2,'Secret List'!$B$3:$B$7,0)

and apply the desired format.