Sumproduct using named ranges and multiple criteria

[Guest]

Hello all I'm currently in a time crunch at work and need some help
developing a forumula to give me totals based of three criteria. I'm really
in a bind here and could use some help as fast as possible.

I'm using named ranges for my columnar data, and I'm attempting to use VBA
combo boxes to give me two of my search criteria. The third criteria is the
in adjacent cells.


My data sources and logic path is Contract Number --> Fiscal Year -->
Calendar Month.

My data is pulled from an Access database through several queries and the
ranges: conNum, finDate, and Total are defined using =offset.

What I'm trying to do, is total the invoices that I have by month, and the
user selects the contract that they want with the first combo box, and this
triggers the code to populate the second combo box with the fiscal years that
the contract has invoices for.

I've tried a couple different formula's but they always return an error.
For example this formula returns a #value error. I've done through each step
in the evaluations, and it's not returning the correct values for some of the
logic tests and then ultimately returns a #value when I get to the month
criteria.
=SUM((conNum="varCon")*(YEAR(finDate)="varYear")*(MONTH(finDate)+6=MONTH(Budget!$A12))*Total)

I've also tried this formula, which initially gave me a #name error but now
just returns 1/0/1900 no matter how I adjust the month cell.
=SUMPRODUCT(--(conNum="varCon"),--(YEAR(finDate)="varYear"),--(MONTH(finDate)+6="'Budget'!$A15"),Total)

Both of these formula give "User type not defined" errors from VBA and I'm
not entirely sure where to start looking and how to fix it. The reference to
budget!$A15 is a cell reference to the cell where the month names are stored.
A6:A17 where A6 = July and I dragged down the rest of the months. I'm
placing the formulas in B6:B17 hence the relative cell reference so I can
just drag down the forumula. All of the user configurable and interace is on
worksheet "budget" and I have the ranges and queries on worksheet "data".
If you require anymore information please don't hesitate to ask.

I've looked over several website and many of the links that have been posted
previously in this newgroup, and I've tried modifying several of the formulas
that I've come accross, but I'm still hitting this brick wall.


On a side note, I'm having a seperate issue with a VBA command button and
updating the .connection properties of some pivot tables and queries. The
thread is located here:
http://www.microsoft.com/office/com...7ac79b-fdc1-4410-9df6-c7d9228b0f25&sloc=en-us

 

[Guest]

Ok, so I remembered that I'd entered another sumproduct formula earlier in
the sheet. It appears that this was the cause of the user type error.

 

[Dave Peterson]

1/0/1900 is 0 that's formatted as a date.

And in this portion of your formula:
(YEAR(finDate)="varYear")

You're comparing a number (year(findate)) to a string "varYear"--the actual
letters v-a-r-y-e-a-r. This will never happen, so the total will be 0.

And in this expression:
(conNum="varCon")
you're really comparing whatever conNum is to the string "varCon"

I don't have a response for your other question.

 

[Guest]

Hehe, sorry. I remembered one mroe thing that I tried. varCon and varYear
are string vars set equal to budget.cmbBudget.Value and budget.cmbYear.Value
respectively. These are my two combo boxes. I tried inputing the .value
strings in the formula directly, but I got that same errors I'm getting now
and I figured it'd be easier for debugging to just deal with the string vars.

 

[Guest]

Dave thanks for the reply.

Thanks for the clarification on the use of double quotes. I'd misunderstood
my research to mean that if the output of the variable was text that I had to
enclose with "", but in reality it just encloses a text string.

The ranges that I'm using are (all ranges are columnar and the same length):
conNum - which is populated with contract names, all text strings;
finDate - this is a date range, actual dates not text
Total - this is the range with all actual invoice numbers in it

My variables are:

varCon - I set this equal to budget.cmbBudget.Value Where budget is the
sheet where the combobox cmbBudget is located. This box contains the same
contracts as conNum, except no duplicates

varYear - I set this equal to budget.cmbYear.Value It's located on the
same sheet and the source data are numbers, not dates Does the combobox
..Value property always output text strings or is it whatever the source data
it?

I tried getting rid of the double quotes from the variables, and instead of
YEAR(finDate) I'm using Format(finDate,"yyyy") but it didn't work.

Any suggestions on where to go from here? Thanks for the help so far.

~Andrew Gates

 

[Dave Peterson]

Are you trying to use VBA to build that formula and then put it in a cell?

Dim myFormula as String

'Since ConNum is a list of text entries and varCon is text, then varCon has to
'be enclosed in double quotes
myformula _
= "=SUMPRODUCT(--(conNum=" & chr(34) & varCon & chr(34) & ")," _
& "--(YEAR(finDate)=" & varYear & ")," _
& "--(MONTH(finDate)+6="'Budget'!$A15"),Total)"

worksheets("someworksheetname").range("someaddress").formula = myformula

Both suggestions are untested. I didn't take the time to create range names or
a userform.