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.


A.Gates wrote:

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 he
http://www.microsoft.com/office/comm...f25&sloc=en-us

--

Dave Peterson