Consulting

Results 1 to 7 of 7

Thread: Comparing a range against a range

  1. #1

    Comparing a range against a range

    Very nice,

    xld I was wondering what Am I doing wrong here

    =IF(N13<>0,SUM(E7:E507),SUMPRODUCT(--(E7:E507=(MID(E7:E507,2,1)="1")),E7:E507))
    thanks
    Feedback is the best way for me to learn


    Follow the Armies

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    You don't compare the whole range against the whole range against a value, just a value.

    Maybe
    =If(N13<>0,SUM(E7:E507),SUMPRODUCT(--(MID(E7:E507,2,1)="1"),E7:E507))
    What are you trying to achieve?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    I want to add the values in Range E7:E507 as long as they have second digit "1" and Cell N13 is empty, Otherwise sum everything in range E7:E507

    Thanks
    Feedback is the best way for me to learn


    Follow the Armies

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    Quote Originally Posted by fredlo2010
    I want to add the values in Range E7:E507 as long as they have second digit "1" and Cell N13 is empty, Otherwise sum everything in range E7:E507

    Thanks
    Did you try what I suggested?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    I am a little ashamed to admit this but I was comparing the wrong ranges. It was supposed to be compare Range "A7:A507" and Add range "E7:E507".

    So yes xld your formula works perfectly. I was not getting it right the first time I think I understand it better now. Yet practice makes the master. I fixed the issue with my ranges and the if statement a little bit to fit my needs? But who needs a formula when you can get a mega formula to make all the job for you and make sure all bases are covered.

    So here is my final formula. I tried to Improve it to make it shorter but I failed.

    =IF(N13=0,SUM(E7:E507),SUMPRODUCT(--(MID(A7:A507,2,1)="1"),--(A7:A507<>5114),--(A7:A507<>5124),--(A7:A507<>5113),--(A7:A507<>5123),--(A7:A507<>5116),--(A7:A507<>5118),E7:E507))
    One thing is for sure it works

    Ps. Sorry I took so long to respond busy day today at work
    Feedback is the best way for me to learn


    Follow the Armies

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    You could use

    =IF(N13=0,SUM(E7:E20),
    SUMPRODUCT(--(MID(A7:A20,2,1)="1"),--(NOT(ISNUMBER(MATCH(A7:A20,{5113,5114,5116,5118,5123,5124},0)))),E7:E20))
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    Thanks xld,

    It works perfectly. Instead of trying to learn VBA I should learn Excel functions first. I never used NOT before ever.
    Feedback is the best way for me to learn


    Follow the Armies

Posting Permissions

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