less than equal to

DStan · Mar 14th, 2002, 01:55 PM

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?

Aladin Akyurek · Mar 14th, 2002, 03:50 PM

Quote:

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 ]

Mar 15th, 2002, 07:16 AM

Quote:

On 2002-03-14 14:50, Aladin Akyurek wrote:

Quote:

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

Aladin Akyurek · Mar 15th, 2002, 08:01 AM

Quote:

On 2002-03-15 06:16, Anonymous wrote:

Quote:

On 2002-03-14 14:50, Aladin Akyurek wrote:

Quote:

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

DStan · Mar 15th, 2002, 08:44 AM

Quote:

On 2002-03-15 07:01, Aladin Akyurek wrote:

Quote:

On 2002-03-15 06:16, Anonymous wrote:

Quote:

On 2002-03-14 14:50, Aladin Akyurek wrote:

Quote:

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

Aladin Akyurek · Mar 15th, 2002, 09:00 AM

Quote:

On 2002-03-15 07:44, DStan wrote:

Quote:

On 2002-03-15 07:01, Aladin Akyurek wrote:

Quote:

On 2002-03-15 06:16, Anonymous wrote:

Quote:

On 2002-03-14 14:50, Aladin Akyurek wrote:

Quote:

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

DStan · Mar 15th, 2002, 09:09 AM

Quote:

On 2002-03-15 08:00, Aladin Akyurek wrote:

Quote:

On 2002-03-15 07:44, DStan wrote:

Quote:

On 2002-03-15 07:01, Aladin Akyurek wrote:

Quote:

On 2002-03-15 06:16, Anonymous wrote:

Quote:

On 2002-03-14 14:50, Aladin Akyurek wrote:

Quote:

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

Aladin
Your supposition is correct. It works. I understand now what you were asking me, you just wanted 5 examples of data. I'm sorry I didn't catch on sooner. Thank you for your patience and vast knowledge.
David Stanley

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 14th, 2002, 01:55 PM	#1
DStan New Member Join Date: Mar 2002 Posts: 6	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?