Comparing a range against a range

Very nice,

xld I was wondering what Am I doing wrong here

Code:

---

=IF(N13<>0,SUM(E7:E507),SUMPRODUCT(--(E7:E507=(MID(E7:E507,2,1)="1")),E7:E507))

---

thanks

You don't compare the whole range against the whole range against a value, just a value.

Maybe

Code:

---

=If(N13<>0,SUM(E7:E507),SUMPRODUCT(--(MID(E7:E507,2,1)="1"),E7:E507))

---

What are you trying to achieve?

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

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?

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.

Code:

---

=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 :(

You could use

Code:

---

=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))

---

Thanks xld,

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