Formula reporting OR rather than AND

[dazbear]

Ive been trying to work out where I have gone wrong below, but cant see for looking

The formula seems to be counting whether cells meet one OR the other criteria, rather than both.

In the case below it is reporting 4 instances, which is right for EC16:120=15 OR EE16:120>29, but there are actually 0 instances of both.

=SUMPRODUCT(SUBTOTAL(2,OFFSET('Y6 R'!$EC$16,ROW('Y6 R'!$EC$16:$EC$120)-ROW('Y6 R'!$EC$16),0)),('Y6 R'!$EC$16:$EC$120=15)*('Y6 R'!$EE$16:$EE$120>29))

Any ideas?

The reason for it not being a simple formula is that is has to work under filtering.

Thanks

[Ron Coderre]

In my testing of your formula, it's returning correct values.

Can you post a sample workbook that exhibits the problem you're seeing?

[dazbear]

Thanks Ron

Posting is a bit tricky as this formula references data on another sheet, which in turn references data on another workbook :os

It is strange, as only happening in some cells and not in others, despite having almost the same formula - all in the same column though.

For example the formula below works beautifully....

=SUMPRODUCT(SUBTOTAL(2,OFFSET('Y6 R'!$EC$16,ROW('Y6 R'!$EC$16:$EC$120)-ROW('Y6 R'!$EC$16),0)),('Y6 R'!$EC$16:$EC$120>17)*('Y6 R'!$EE$16:$EE$120>=30))

[Ron Coderre]

I'm guessing the problem is not with the formula, but with the linking.
FWIW...I NEVER link a workbook to another workbook. Links seem convenient, at first, but they're just waiting to fail at the worst possible time.

[dazbear]

I think I sussed where the issue lies.

Sometimes, while there is a value in EC the corresponding EE cell may be blank.

The formula seems to be seeing this as >29 and adding it to the total.

Any ideas?

[Ron Coderre]

If the EE value is blank...the formula works properly.
However. a text blank ("") is NOT a blank. It's empty text.
Try this:

=SUMPRODUCT(SUBTOTAL(2,OFFSET('Y6 R'!$EC$16,ROW('Y6 R'!$EC$16:$EC$120)-ROW('Y6 R'!$EC$16),0))
,('Y6 R'!$EC$16:$EC$120=15)*('Y6 R'!$EE$16:$EE$120<>"")*('Y6 R'!$EE$16:$EE$120>29))

Better?

[dazbear]

It just reports 0 all the time now

You have hit the nail on the head though!

Cells in EC and EE do populate a text blank "" if in error, to stop #N/A appearing - which really messes the whole lot up.

=IF(ISERROR((CM23)),"",((CM23)))

Perhaps it is here I should be looking more than the formula posted earlier?