C
|| cypher ||
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
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