Multiple IF Statements in COUNTIFS formula referencing different cells (is this even possible?!)

[stbrooks13]

I'm not sure if this is possible, so here goes...

I am creating a scorecard in excel that measures recruiting data points by my firm's region/market/office. I would like to use the same scorecard for all three.

I have the field (W4) to select whether it's a region/market/office, and (W3) to select the actual region/market/office.

Here's an example of one of the formulas I'm toying around with to calculate offers accepted within our fiscal year that we received from an "other" source (by region/market/office):

COUNTIFS(Source,"Other",OfferAccepted,"<42156",OfferAccepted,">41790",IF(W4=Regions,"Region,W3",IF(W4=Markets,"Market,W3","Office,W3")))

I guess I could always do something to the following effect, but even that's resulting in "too few argument" errors.

=IF(W4="Regions",COUNTIFS(Source,"Other",OfferAccepted,"<42156",OfferAccepted,">41790",Region,W3),IF(W4="Markets",COUNTIFS(Source,"Other",OfferAccepted,"<42156",OfferAccepted,">41790",Market,W3),IF(W4="Local",COUNTIFS(Source,"Other",OfferAccepted,"<42156",OfferAccepted,">41790",Office,W3,0)))

 

[ukmikeb]

Hi

Probably :-

COUNTIFS(Source,"Other",OfferAccepted,"<42156",OfferAccepted,">41790",INDEX((Region,Market,Office),,,MATCH(W4,{"Regions","Markets","Local"},0)),W3)

hth