On 2002-03-15 08:00, Aladin Akyurek wrote:
On 2002-03-15 07:44, DStan wrote:
On 2002-03-15 07:01, Aladin Akyurek wrote:
On 2002-03-15 06:16, Anonymous wrote:
On 2002-03-14 14:50, Aladin Akyurek wrote:
On 2002-03-14 12:55, DStan wrote:
I am using this function to sum column I that meets certain criteria.
=SUMPRODUCT((ISNUMBER(SEARCH("KND*",PRODUCTION!$A$1:$A$49027)))*(ISNUMBER(SEARCH("FRAMING*",PRODUCTION!$E$1:$E$49027)))*(ISNUMBER(SEARCH($D$1,PRODUCTION!$G$1:$G$49027)))*(ISNUMBER(SEARCH($F$1,PRODUCTION!$F$1:$F$49027))),(PRODUCTION!$I$1:$I$49027))
$D$1 is accounting period and works fine for month to date numbers. My problem is year to date numbers. I would like to base it off $D$1 and use<=$D$1 Can someone help me incorporate<= into this function?
Can you give example values that (might) go into D1?
Addendum: What is/can be in E1?
I notice you have big ranges to test? Are they really that big?
Aladin
This message was edited by Aladin Akyurek on 2002-03-14 15:04
Aladin
First my ranges are not that big, I got your post yesterday on how to only use range that is imported from database and intend to use it.
D1 is a number. 1-12 for g/l periods.
E1 is text to describe a process we do. I am filtering records for those that meet that process.
I really appreciate your responses.
Thank You
David Stanley
David,
I understood D1 as having a numeric value of 1 to 12.
May I ask a bit more: 5 values from PRODUCTION!$G$1:$G$49027 ?
Aladin
Aladin,
I am not sure I understand your question but I will answer what I thunk your asking (sorry for my ignorance)
what I want to do is compare cell D1 with column G on Production worksheet. I enter the period (1-12) on worksheet, and it uses that number to sum column I where Column G equals D1. For month numbers all I have to do is match D1. For year to date numbers, my idea was to search production!G for<= D1. I hope that helps.
David Stanley
David,
Five example values from G-range would have helped me more. Now I have to suppose that you have integer values (probably month numbers) in that range. If my supposition is correct, change the formula to:
=SUMPRODUCT((ISNUMBER(SEARCH("KND*",PRODUCTION!$A$1:$A$49027)))*(ISNUMBER(SEARCH("FRAMING*",PRODUCTION!$E$1:$E$49027)))*(PRODUCTION!$G$1:$G$49027<=$D$1)*(ISNUMBER(SEARCH($F$1,PRODUCTION!$F$1:$F$49027))),(PRODUCTION!$I$1:$I$49027))
The change consists of:
(PRODUCTION!$G$1:$G$49027<=$D$1)
Aladin