If/then formula

C

caveman

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
 
T

Toppers

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

caveman

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
 
C

caveman

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

hans.van.snick

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








- Tekst uit oorspronkelijk bericht weergeven -
Click to expand...

is an Array a solution?

as an example:

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

reg Hans
 
Top