If And or uhhhm Or

JazzSP8

Well-known Member
Joined
Sep 30, 2005
Messages
1,227
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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 :confused:

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!
 
Upvote 0
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 :oops:

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?

:unsure:
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top