If/then formula
 

Not sure to go about this.

I need a formula that states....

If in the range of A1:A1424 there is "Baltimore" AND if in the range of
D1:D1424 there is "physician"......then add the contents of the cell in that
row that is in column F.

thanks

If/then formula
 

You probably want a sumproduct formula similar to this...

=sumproduct(--(A1:A1424 = "Baltimore"), --(D1:D1424 = "physician"), F1:F1424)

Check out this link...
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"caveman" wrote:

Not sure to go about this.

I need a formula that states....

If in the range of A1:A1424 there is "Baltimore" AND if in the range of
D1:D1424 there is "physician"......then add the contents of the cell in that
row that is in column F.

thanks

If/then formula
 

=INDEX(F1:F1424,MATCH(1,(A1:A1424="Baltimore")*(D1 :D1424="physician"),0))

Enter with Ctrl+Shift+Enter

Better to put "Baltimore" & "physician" in Cells:

=index(f1:F1424,match(1,(a1:A1$24=X1)*(D1:D1424=X2 ),0))

If there is more than one match, this will find the first

OR

=SUMPRODUCT(--(A1:A1424="Baltimore"),--*(D1:D1424="physician"),F1:F1424)


If there is more than one match, SUMPRODUCT will total all occurences.


"caveman" wrote:

Not sure to go about this.

I need a formula that states....

If in the range of A1:A1424 there is "Baltimore" AND if in the range of
D1:D1424 there is "physician"......then add the contents of the cell in that
row that is in column F.

thanks

If/then formula
 

hey great, that worked.....EXCEPT...

I realize now I have to add a third condition, let's say...surgery or
geriatrics.....

using the
=SUMPRODUCT(--(A1:A1424="Baltimore"),--*(D1:D1424="physician"),F1:F1424)
formula works great, except when I add, let's say, the surgery condition.

This is what I've done...

=SUMPRODUCT(--(A$1:A$1424="Baltimore"),--($A$1:A$1424="Surgery")--(D$1:D$1424="physician"),F$1:F$1424)

What am I doing wrong?

(are the -- supposed to be *? I replaced the dashes for multiply, but then I
get an error message....I'm understandiing the principle, just not doing
something right)

thanks



"Toppers" wrote:

=INDEX(F1:F1424,MATCH(1,(A1:A1424="Baltimore")*(D1 :D1424="physician"),0))

Enter with Ctrl+Shift+Enter

Better to put "Baltimore" & "physician" in Cells:

=index(f1:F1424,match(1,(a1:A1$24=X1)*(D1:D1424=X2 ),0))

If there is more than one match, this will find the first

OR

=SUMPRODUCT(--(A1:A1424="Baltimore"),--*(D1:D1424="physician"),F1:F1424)


If there is more than one match, SUMPRODUCT will total all occurences.


"caveman" wrote:

Not sure to go about this.

I need a formula that states....

If in the range of A1:A1424 there is "Baltimore" AND if in the range of
D1:D1424 there is "physician"......then add the contents of the cell in that
row that is in column F.

thanks

If/then formula
 

oh...I left out the comma after the closed parenthesis after surgery...it's
working now...super. thanks

"caveman" wrote:

hey great, that worked.....EXCEPT...

I realize now I have to add a third condition, let's say...surgery or
geriatrics.....

using the
=SUMPRODUCT(--(A1:A1424="Baltimore"),--*(D1:D1424="physician"),F1:F1424)
formula works great, except when I add, let's say, the surgery condition.

This is what I've done...

=SUMPRODUCT(--(A$1:A$1424="Baltimore"),--($A$1:A$1424="Surgery")--(D$1:D$1424="physician"),F$1:F$1424)

What am I doing wrong?

(are the -- supposed to be *? I replaced the dashes for multiply, but then I
get an error message....I'm understandiing the principle, just not doing
something right)

thanks



"Toppers" wrote:

=INDEX(F1:F1424,MATCH(1,(A1:A1424="Baltimore")*(D1 :D1424="physician"),0))

Enter with Ctrl+Shift+Enter

Better to put "Baltimore" & "physician" in Cells:

=index(f1:F1424,match(1,(a1:A1$24=X1)*(D1:D1424=X2 ),0))

If there is more than one match, this will find the first

OR

=SUMPRODUCT(--(A1:A1424="Baltimore"),--*(D1:D1424="physician"),F1:F1424)


If there is more than one match, SUMPRODUCT will total all occurences.


"caveman" wrote:

Not sure to go about this.

I need a formula that states....

If in the range of A1:A1424 there is "Baltimore" AND if in the range of
D1:D1424 there is "physician"......then add the contents of the cell in that
row that is in column F.

thanks

If/then formula
 

On 30 aug, 22:50, caveman wrote:
hey great, that worked.....EXCEPT...

I realize now I have to add a third condition, let's say...surgery or
geriatrics.....

using the
=SUMPRODUCT(--(A1:A1424="Baltimore"),--*(D1:D1424="physician"),F1:F1424)
formula works great, except when I add, let's say, the surgery condition.

This is what I've done...

=SUMPRODUCT(--(A$1:A$1424="Baltimore"),--($A$1:A$1424="Surgery")--(D$1:D$14*24="physician"),F$1:F$1424)

What am I doing wrong?

(are the -- supposed to be *? I replaced the dashes for multiply, but then I
get an error message....I'm understandiing the principle, just not doing
something right)

thanks



"Toppers" wrote:
=INDEX(F1:F1424,MATCH(1,(A1:A1424="Baltimore")*(D1 :D1424="physician"),0))

Enter with Ctrl+Shift+Enter

Better to put "Baltimore" & "physician" in Cells:

=index(f1:F1424,match(1,(a1:A1$24=X1)*(D1:D1424=X2 ),0))

If there is more than one match, this will find the first

OR

=SUMPRODUCT(--(A1:A1424="Baltimore"),--*(D1:D1424="physician"),F1:F1424)

If there is more than one match, SUMPRODUCT will total all occurences.

"caveman" wrote:

Not sure to go about this.

I need a formula that states....

If in the range of A1:A1424 there is "Baltimore" AND if in the range of
D1:D1424 there is "physician"......then add the contents of the cell in that
row that is in column F.

thanks- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

is an Array a solution?

as an example:

{=SUM(($D$4:$D$11=D13)*($E$4:$E$11=E13)*($F$4:$F$1 1=F13)*($G$4:$G
$11))}

reg Hans