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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

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,850
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,119,185
Messages
5,576,582
Members
412,735
Latest member
jwoods1204
Top