PDA

View Full Version : [SOLVED] Comparing a range against a range



fredlo2010
06-29-2012, 05:49 PM
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

Bob Phillips
07-03-2012, 12:47 AM
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?

fredlo2010
07-03-2012, 05:43 AM
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

Bob Phillips
07-03-2012, 06:32 AM
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?

fredlo2010
07-03-2012, 05:20 PM
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 :(

Bob Phillips
07-04-2012, 12:44 AM
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))

fredlo2010
07-04-2012, 08:48 PM
Thanks xld,

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