# If And or uhhhm Or

#### JazzSP8

##### Well-known Member
Hey Guys

I am in the middle of something pretty complex and probably way out of my league at this point, its late and I need some shut eye, but I was hoping someone could answer this for me;

I have this formula;

{=SUM(IF(Source!\$B\$1:\$B\$20000="T ",IF(Source!\$D\$1:\$D\$20000="QUO ",OR(Source!\$H\$1:\$H\$2000="HLD ",Source!\$I\$1:\$I\$20000,0),0)))}

I am checking for an absolute contiion of "T" in Col B and want to know if Cold D or H meet a condition (QUO and HLD respectively), and if so then add Col I.

What I am wondering is, if Col D and Col H are true, would Col I be added twice?

The formula as it stands does its job, but if it is only added once then I could use similar formulas to save myself a lot of time further down the line, if not I know I need to use an "And" a LOT of times

As always, thanks in advance for any help given

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Try...

=SUM(IF(Source!\$B\$1:\$B\$20000="T",IF((Source!\$D\$1:\$D\$20000="QUO")+(Source!\$H\$1:\$H\$2000="HLD"),Source!\$I\$1:\$I\$20000)))

...confirmed with CONTROL+SHIFT+ENTER. Note that if both Column D and H are true, Column I would be added only once.

Hope this helps!

Hey Domenic

Thanks for the quick response, I will only get to that part of what I want to do tomorrow, without trying it and with only looking at it, how would that be different to;

{=SUM(IF(Source!\$B\$1:\$B\$20000="T ",IF(Source!\$D\$1:\$D\$20000="QUO ",AND(Source!\$H\$1:\$H\$2000="HLD ",Source!\$I\$1:\$I\$20000,0),0)))}

Sorry if I am being dumb, I just need to make this right and your final statement said if both column D and H are true.

Actually, no, I probably didn't explain myself properly, its late

The formula I would like to use would go something like;
=SUM(IF(Source!\$B\$1:\$B\$20000="T ",IF(Source!\$D\$1:\$D\$20000="QUO ",OR(Source!\$D\$1:\$D\$20000="SHO",OR(Source!\$H\$1:\$H\$20000="HLD ",OR(Source!\$H\$1:H\$20000="CRD",Source!\$I\$1:\$I\$20000,0),0)))))

I just need to know if more than one "OR" condition was met, would it be added more than once?

I need one £ figure which is in Col I - No matter which of those conditions is met.

Hope that explains it a little better?

To sum Column I where the corresponding value in Column B equals 'T', Column D equals 'QUO', and Column H equals 'HLD', try...

Code:
``=SUM(IF(Source!\$B\$1:\$B\$20000="T",IF(Source!\$D\$1:\$D\$20000="QUO",IF(Source!\$H\$1:\$H\$2000="HLD",Source!\$I\$1:\$I\$20000))))``

To sum Column I where the corresponding values in Column B equals 'T', Column D equals 'QUO' or 'SHO', and Column H equals 'HLD' or 'CRD', try...

Code:
``=SUM(IF(Source!\$B\$1:\$B\$20000="T",IF((Source!\$D\$1:\$D\$20000="QUO")+(Source!\$D\$1:\$D\$20000="SHO"),IF((Source!\$H\$1:\$H\$20000="HLD")+(Source!\$H\$1:H\$20000="CRD"),Source!\$I\$1:\$I\$20000))))``

Hope this helps!

Hey Domenic

OK - Thanks, I am going to be working on this, this morning so hopefully I should be able to use your second formula and it will give me the results that I am expecting, shall let you know how I get on

Thanks for your help on this.

Replies
5
Views
499
Replies
11
Views
734
Replies
5
Views
229
Replies
0
Views
281
Replies
10
Views
514

1,214,713
Messages
6,121,042
Members
449,006
Latest member
cthorne1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back