SUMIF/COUNTIF with an additional criteria
 

I have a three column sheet. named MAIN
COLUMNS:
a.) AGENT (Validated with a pulldown list)
b.) STATUS (Validated with a pulldown list)
c.) PRICE

I have another sheet named STATS
Cell STATS!B1 contains the agents name (Validated with a pulldown list)

Cell STATS!B2 I want to count how many active listings the agent in
STATS!B1 has
=COUNTIF(MAIN!A2:A61,STATS!B1)

Cell STATS!B3 I want to sum the entire value of all the active listings
agent in STATS!B1 has.
=SUMIF(MAIN!A2:A61,STATS!B1,MAIN!C2:C61)

AVAILABLE STATUS VALUES:
ACTV
DFT
CNT CS
CNT CC
CNT PS
CNT SI
PEND
WDRWN
SOLD
LEASED
EXP



Based on those values above, what I am hoping to create is a SUMIF and
COUNTIF tally based on the agents name where the STATUS equals ACTV, DFT,
CNT CS, CNT CC, CNT PS, CNT SI, or PEND.

I want to prevent values from being SUMMED and COUNTED if the status equals
WDRWN, SOLD, LEASED or EXP

I am not sure how to incorporate this additional requirement into the
corresponding formulae. Any assistance would be greatly appreciated.

-cypher

Hi
use SUMPRODUCT. See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany


|| cypher || wrote:
I have a three column sheet. named MAIN
COLUMNS:
a.) AGENT (Validated with a pulldown list)
b.) STATUS (Validated with a pulldown list)
c.) PRICE

I have another sheet named STATS
Cell STATS!B1 contains the agents name (Validated with a pulldown
list)

Cell STATS!B2 I want to count how many active listings the agent in
STATS!B1 has
=COUNTIF(MAIN!A2:A61,STATS!B1)

Cell STATS!B3 I want to sum the entire value of all the active
listings agent in STATS!B1 has.
=SUMIF(MAIN!A2:A61,STATS!B1,MAIN!C2:C61)

AVAILABLE STATUS VALUES:
ACTV
DFT
CNT CS
CNT CC
CNT PS
CNT SI
PEND
WDRWN
SOLD
LEASED
EXP



Based on those values above, what I am hoping to create is a SUMIF
and
COUNTIF tally based on the agents name where the STATUS equals ACTV,
DFT, CNT CS, CNT CC, CNT PS, CNT SI, or PEND.

I want to prevent values from being SUMMED and COUNTED if the status
equals WDRWN, SOLD, LEASED or EXP

I am not sure how to incorporate this additional requirement into the
corresponding formulae. Any assistance would be greatly appreciated.

-cypher