Thanks:  0
Likes:  0

# Thread: less than equal to

1. 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?

2. 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?

[ This Message was edited by: Aladin Akyurek on 2002-03-14 15:04 ]

3. 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?

[ This Message was edited by: Aladin Akyurek on 2002-03-14 15:04 ]
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.

Thank You
David Stanley

4. 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?

[ This Message was edited by: Aladin Akyurek on 2002-03-14 15:04 ]
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.

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 ?

5. 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?

[ This Message was edited by: Aladin Akyurek on 2002-03-14 15:04 ]
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.

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 ?

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

6. 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?

[ This Message was edited by: Aladin Akyurek on 2002-03-14 15:04 ]
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.

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 ?

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)

7. 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?

[ This Message was edited by: Aladin Akyurek on 2002-03-14 15:04 ]
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.

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 ?

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)

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

## User Tag List

#### Posting Permissions

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