Late night SumProduct problems

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,255
Good evening,

I'm having a problem with the following SumProduct formula
Code:
=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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,805
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Probably also worth checking you don't have circular references and, of course, that calculation is set to automatic. ;)
 

Trouttrap2

Well-known Member
Joined
May 11, 2010
Messages
612
Do you get the same result with =COUNTIFS(Col_DB_Prefix,F5,Col_DB_P,"Y",Col_DB_Category,"Foreign Private")
 

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,255
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,073
Messages
5,570,032
Members
412,305
Latest member
NoExcelNoFun
Top