If And or uhhhm Or

[JazzSP8]

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

 

[Domenic]

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!

 

[JazzSP8]

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?

 

[Domenic]

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

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

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

 

[JazzSP8]

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.