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

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

=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

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

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

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