If/then formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
oh...I left out the comma after the closed parenthesis after surgery...it's
working now...super. 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$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
 
Back
Top