Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: less than equal to

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,637
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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 ]

  3. #3
    Guest

    Default

    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

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,637
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,637
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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


  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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