Late night SumProduct problems

[NewOrderFac33]

Good evening,

I'm having a problem with the following SumProduct formula

=SUMPRODUCT(--(Col_DB_Prefix=F5),--(Col_DB_P="Y"),--(Col_DB_Category="Foreign Private"))

This formula returns 1, yet there are absolutely no records in my database that match these criteria.

There are lots of similarly structured formulae in the workbook that are returning erroneous values too.
The Col_DB_ range names are created by a routing that I have used in countless other applications and are all the same size and shape.

Can anyone suggest anything that might be wrong with the formula or suggest, from past experience, any worksheet issues that might lead to my SumProduct formulae returning incorrect totals?

Thanks in advance

Pete

 

[XOR LX]

Hi.

Very difficult to say without seeing the actual sheet.

Though since Excel is convinced that there is one row which matches all three criteria, it may be worth interrogating it as to which row that is. At least then you can manually go to that line and investigate.

This array formula** should do that:

=MIN(IF(Col_DB_Prefix=F5,IF(Col_DB_P="Y",IF(Col_DB_Category="Foreign Private",ROW(Col_DB_Category)))))

Late night indeed!

Cheers


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[RoryA]

Probably also worth checking you don't have circular references and, of course, that calculation is set to automatic.

 

[Trouttrap2]

Do you get the same result with =COUNTIFS(Col_DB_Prefix,F5,Col_DB_P,"Y",Col_DB_Category,"Foreign Private")

 

[NewOrderFac33]

Thanks all for your suggestions. As I'm soft and it's gone midnight, I'm now tucked up in bed, but will investigate all your suggestions tomorrow. I do recall that it all worked OK with SUMIFS previously. Perhaps I went a bit sumproduct happy and need to revert to SUMIFS.

Thanks

Pete