sumif / countif question
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
|