# Thread: Comparing a range against a range

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

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

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

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

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

6. 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))```

7. Thanks xld,

It works perfectly. Instead of trying to learn VBA I should learn Excel functions first. I never used NOT before ever.

#### Posting Permissions

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