less than equal to

DStan

New Member
Joined
Mar 12, 2002
Messages
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?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
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
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top