Thread: Functions that return values

1. Registered User
Join Date
Mar 2004
Posts
361

Unanswered: Functions that return values

I am trying to write a function that returns a value that meets certain criteria. I have a worksheet that has about 40 rows and I want to return the number to a different worksheet that meets two criteria. What function would you recommend.

Ex if value in column A = Tree and value in column B = 1999 then total and show on a different worksheet.

Hope that is clear enough.

Thanks

2. Registered User
Join Date
Jan 2004
Location
Aberdeen, Scotland
Posts
1,067

Re: Functions that return values

Is this what your after?

=SUMPRODUCT((A1:A40 = "Tree")*(B1:B40 = 1999)*(C1:C40))

where A has to equal Tree, B has to equal 1999 and C is a Number
This sums the numbers when A is True and B is True sum the values in C

David

3. Registered User
Join Date
Mar 2004
Posts
361
Thanks that perfect

4. Registered User
Join Date
Oct 2003
Posts
1,091
Just as a reminder, when you use SUMPRODUCT, you have to specify the rows. You cannot use A:A (as you can in other functions), it has to be A2:A50 or something like that.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•