Count if array incl blank values as criteria - not working ?

[fallwire]

Hello,

I have a COUNTIF array formula in column A of the attached that I can't seem to get to work.

Cell A35 is meant to equal 2, but it is currently equalling 8.
Importantly, it uses a Offset,Lookup to focus only on the rows beneath the first instance of "Career:*" in column O looking upward.

This formula is meant to count the rows that do the following:
- column C >0 & <42
- column E >0
- column I >0
- column M = "" (eg.blank)
AND COUNT qualifying rows according to cells in Z that have a value.
Hence, of those rows between 28 to 35 (which the Offset,Lookup focuses upon) the answer to this should be "2".

I've tried ordering the criteria to see if that changes things but still not getting the desired result.

Can anyone put me out of my misery and advise what is wrong with how I have set this up..? Help would be greatly appreciated

Thanks
Trent

[daddylonglegs]

Specifically I think the problem is that column E has some "formula blanks" "" - when you check to see that col E > 0 then text values like "" are deemed to be > than any number, so you can add another condition.........but calculating the range each time using OFFSET may be a costly way to do this. You can check that the row numbers are greater than the last instance of "Career:....." and then just apply the other conditions to all rows, i.e. this formula in row 2 copied down

=IF(K2="MOST RECENT",COUNT(IF((ROWS(A$2:A2)>MAX(IF(LEFT(N$2:N2,7)="career:",ROW(N$2:N$2)-ROW(N$2)+1)))*(C$2:C2>0)*(C$2:C2<42)*(E$2:E2>0)*(E$2:E2<>"")*(I$2:I2>0)*(I$2:I2<>"")*(M$2:M2=""),B$2:B2)),"")

confirmed with CTRL+SHIFT+ENTER

[XOR LX]

Hi,

A couple of things:

Firstly, you do not have a clause in this formula which is checking whether the relevant entries in column C are >0.

Secondly, since your entries in column E are the results of formulae which, in certain conditions will return "", using >0 as the qualifier in your formula will give incorrect results (since, in Excel, ="">0 will return TRUE). You need to amend this condition to <>"".

All in all:

=IF(K35="MOST RECENT",COUNT(IF((OFFSET($E$1,LOOKUP(2,1/ISNUMBER(SEARCH("Career:*",N14:N35)),ROW(N14:N35)),0):E35<>"")*(OFFSET($C$1,LOOKUP(2,1/ISNUMBER(SEARCH("Career:*",N14:N35)),ROW(N14:N35)),0):C35>0)*(OFFSET($C$1,LOOKUP(2,1/ISNUMBER(SEARCH("Career:*",N14:N35)),ROW(N14:N35)),0):C35<42)*(OFFSET($M$1,LOOKUP(2,1/ISNUMBER(SEARCH("Career:*",N14:N35)),ROW(N14:N35)),0):M35="")*(OFFSET($I$1,LOOKUP(2,1/ISNUMBER(SEARCH("Career:*",N14:N35)),ROW(N14:N35)),0):I35>0),(OFFSET($B$1,LOOKUP(2,1/ISNUMBER(SEARCH("Career:*",N14:N35)),ROW(N14:N35)),0):B35))),"")

Regards

[fallwire]

Thank you both. I had always though "" was the same as not entering anything in a cell but I suppose that's an important distinction. Its working now.
And Daddylonglegs solution to my repeated lookup to isolate a range looks like will save me quite a bit of processing power (I had around 12 columns all using those repeated lookups).

Best regards
Trent