Troubleshooting SumProduct #VALUE error

[MicheeBee]

I'm relatively new to using SUMPRODUCT and haven't ever had to troubleshoot the formula when it isn't working. I've got the following construct:

Month: Numbers 1-12
Action: text list of 14 different actions taken (e.g., new, modify, write-off
Type: text list of 5 difference types (e.g., grant, loan, equity gap)
Amount: number (representing the award)

I have 600 rows of data. The four columns are named as listed above. My formula is as follows:

=sumproduct((month=9)*(action="new")*(type="grant")*amount))

Where the red 9 is above I really want to be able to refer to a list where each row represents a different month but that was proving more difficult so I'd be happy with getting the formula to work once then I'd tackle that problem.

Are the text = references what are causing my problem?

I've evaluated each piece of the sumproduct for my entire data table and I should get 6 detail items returning a total of $300,000.

 

[Jonmo1]

Welcome to the board.

A couple possibilities to start with

I assume Month Action Type and Amount are Named Ranges?
Do they refer to ENTIRE columns, like A:A ?
If so, you can't do that with sumproduct in XL versions prior to XL2007
You would have to restrict the references using actual row #s like A1:A100
Even if you have 2007 or higher, It's still not recommended to use the whole column A:A

2. Likely there are formulas returning #VALUE errors in any 1 or more of the cells referenced by the sumproduct formula.

Hope that helps.

 

[Brian from Maui]

Are all the named ranges of equal size?

 

[MicheeBee]

Each of them are Named Ranges, from row 2 to row 600 (i.e., Month= a2:a600, Action=b2:b600, etc). I am using Excel 2007. I don't get any errors when I apply the {(month=9)} as an array formula for each line or for any other subpart of the sumproduct. I get all True or False which I've verified with filters.

Welcome to the board.

A couple possibilities to start with

I assume Month Action Type and Amount are Named Ranges?
Do they refer to ENTIRE columns, like A:A ?
If so, you can't do that with sumproduct in XL versions prior to XL2007
You would have to restrict the references using actual row #s like A1:A100
Even if you have 2007 or higher, It's still not recommended to use the whole column A:A

2. Likely there are formulas returning #VALUE errors in any 1 or more of the cells referenced by the sumproduct formula.

Hope that helps.

 

[Brian from Maui]

The range to sum probably contains text. Try switching to the -- syntax instead of the * syntax.

 

[MicheeBee]

Brian from Maui's idea got me started down the right path. My data set had both 0 and blanks, as it comes from the download. However, if I force the data to hold something then my formula works (YEAH!). Now how do I deal with 0 and null - Amount >0? or Amount <>0?

Thanks for the fabulous help!

 

[Jonmo1]

Change to the -- syntax.
This will treat blanks as 0 (irrelevant to the sum)

=sumproduct(--(month=9),--(action="new"),--(type="grant"),amount))

 

[Brian from Maui]

Try using the comma synatx, like

=SUMPRODUCT(--(Range1=Criteria),--(Range2=Criteria),Range to Sum)

It handles text in the range to sum.

 

[MicheeBee]

A million million thanks. I assumed my problem was in the matching criteria but it was in my dollar amount and your solutions tracked it down.

 

[marknhopgood]

I also had the issue which was solved by making sure my named ranges were equal size.