countifs reading the formula rather than the outcome of the formula

[ifu06416]

Hi there,

I have a countifs function in sheet 1 of a workbook.

This countifs function looks up column AH on sheet 2.

Column AH is an if function which returns the name of the person who is working the case reference outlined in col A of sheet 1. If there is no reference in column A of sheet 1 the IF function returns a blank (ie if(A2="","").

The issue i am having is that I need the countif function to count the "" cells in col AH as blank but it reads the formula in the cell and assess it as being not empty.

is there a way around this?

Regards,

John

 

[Joe4]

Please post your current COUNTIFS function.

 

[cerfani]

use an array formula...

{=SUM((Sheet2!AH:AH<>"")*([COLOR=#0000ff]another test condition[/COLOR]))}

Ctrl+Shift+Enter will add the {}

edit the columns to include the exact rows to make it more efficient

 

[ifu06416]

Making it an array, that was the solution.

Thanks cerfani

 

[RoryA]

There are usually simpler/more efficient ways if you were to post your formula as requested.