This seemingly basic task is making me pull my hair out...

[Satele]

I'm using =SUM(IF( as an array to count the number of rows that meet multiple conditions. The data is based in an external workbook, so I'm stuck with SUM(IF.

When adding a condition to my formula that looks in a particular column for cells containing '0' (zero), things start falling apart. What am I doing wrong?

Formula:

=SUM(IF(Sheet2!B:B=Sheet1!B8,IF(Sheet2!C:C="johnson",IF(Sheet2!D:D<>"",IF((Sheet2!E:E=0),1,0)))))

It's the reference to Sheet2!E:E that is causing the problem and causes an incorrect value.

Thanks!

 

[XOR LX]

Hi,

What type of data does Sheet2 column E consist of? Are there any error values in there?

Also, referencing entire columns in an array formula is not to be recommended, as, unlike e.g. COUNTIF(S)/SUMIF(S), they will calculate over all one million-plus cells in that range, whether beyond your last non-empty cell or not.

Try using suitably large upper range references, e.g. B1:B1000.

I don't understand your reference to external workbooks either. Your formula indicates that both the formula and the data are in the same workbook. Besides, you are not restricted to using an array formula construction even if that were the case: SUMPRODUCT works on closed, external workbooks just as well.

Regards

 

[Satele]

Hi,

Column E consists of numbers, mostly '0', but there may be other two/three digit numbers.

I realise the referencing of columns isn't ideal, and will limit my range to the row requirements once the formula is complete.

Maybe it wasn't the issue with external workbooks causing me to avoid SUMPRODUCT, but there was a reason. There are other formulas in the book where I need to include just part of a cell as a condition, and I think it struggled with that. E.g. column G contains values like 'join one' and 'join two' and one of my conditions is to just return rows with 'join'.

To get back to my original query, is there a way to use the formula in my original post to find rows that match the conditions I laid out - including rows with zero values in column E?

Thanks for your help!

 

[Satele]

So, I've tried doing this with SUMPRODUCT and I still can't seem to use this to find rows where one of the conditions is that a cell value = 0...

=SUMPRODUCT((flatfileExtract!C:C=A5)*(flatfileExtract!B:B="johnson")*(flatfileExtract!E:E<>"")*(flatfileExtract!BI:BI=0)*(flatfileExtract!BJ:BJ=0))

Can anyone help?