how would you use these functions based on 2 criteria?

for example, if a1="m" and b1="x", how would you sum column c looking for
just these 2 criteria in column a and b?

--


Gary

Gary, here is one way, =SUM(IF((A1:A5="m")*(B1:B5="x"),C1:C5)) This is
an array formula use (ctrl+shift+enter)

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Gary Keramidas" wrote in message
...
how would you use these functions based on 2 criteria?

for example, if a1="m" and b1="x", how would you sum column c looking for
just these 2 criteria in column a and b?

--


Gary

Here is a non-array way

=SUMPRODUCT(--(A2:A100=A1),--(B2:B100=B1,C2:C100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul B" wrote in message
...
Gary, here is one way, =SUM(IF((A1:A5="m")*(B1:B5="x"),C1:C5)) This is
an array formula use (ctrl+shift+enter)

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Gary Keramidas" wrote in message
...
how would you use these functions based on 2 criteria?

for example, if a1="m" and b1="x", how would you sum column c looking
for
just these 2 criteria in column a and b?

--


Gary

=SUMPRODUCT(--(A2:A100=A1),--(B2:B100=B1),C2:C100)

Is probably what Bob meant.

As Bob said, this doesn't have to be array entered, however, otherwise it
has all the behavior and restrictions of an array formula.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
Here is a non-array way

=SUMPRODUCT(--(A2:A100=A1),--(B2:B100=B1,C2:C100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul B" wrote in message
...
Gary, here is one way, =SUM(IF((A1:A5="m")*(B1:B5="x"),C1:C5)) This
is
an array formula use (ctrl+shift+enter)

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Gary Keramidas" wrote in message
...
how would you use these functions based on 2 criteria?

for example, if a1="m" and b1="x", how would you sum column c looking
for
just these 2 criteria in column a and b?

--


Gary

i guess i could have explained it better, just thought if it was simple, i
could adapt it.

i b4:b56 there can be 4 choices, one of which is "M". there is also an "N",
"T", or "W" choice for column a.

then i need to look at j4:j56 and see if there is an "x" and add 1 to the
count for. "M", so i know how many x's relate to "M" in column j. then i
need to multiply that count by a constant, 1 in this case, but could be .50,
10, 25 or 50 when i move to columns k thru q. i need to do this for "N",
"T", and "W".

all of these values are on another sheet, this is a summary sheet. in fact
there are 12 sheets, one for each month. i asked a question earlier about a
formula, but just decided to use a formula for each sheet and use a + sign.
made for a long formula, but it worked.

i tried using countif and "and" for the 2 ranges, but i couldn't get it to
work.

sorry

--


Gary


"Tom Ogilvy" wrote in message
...
=SUMPRODUCT(--(A2:A100=A1),--(B2:B100=B1),C2:C100)

Is probably what Bob meant.

As Bob said, this doesn't have to be array entered, however, otherwise it
has all the behavior and restrictions of an array formula.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
Here is a non-array way

=SUMPRODUCT(--(A2:A100=A1),--(B2:B100=B1,C2:C100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul B" wrote in message
...
Gary, here is one way, =SUM(IF((A1:A5="m")*(B1:B5="x"),C1:C5)) This
is
an array formula use (ctrl+shift+enter)

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from
it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Gary Keramidas" wrote in message
...
how would you use these functions based on 2 criteria?

for example, if a1="m" and b1="x", how would you sum column c looking
for
just these 2 criteria in column a and b?

--


Gary