Excel formulas

[Guest]

I am creating a spreadsheet that tracks our current staff across the country.
Is there a way to create a formula that counts from two different fields.

For example I want the total number of people if Column B reads "ATLANTA"
and column F reads "ACTIVE"

I was thinking I could use COUNTIF somehow....but can't quite figure it out

 

[Guest]

One way

=SUMPRODUCT(--(B2:B100="ATLANTA"),--(F2:F100="ACTIVE"))

you could replace the criteria with cell references so it be easier to
change criteria, also note that unlike countif you can't use the whole column
like B;B

Regards,

Peo Sjoblom

 

[Dave O]

You can do this using a variation of SUMPRODUCT(). You'll need to
expand the ranges to reflect your application, but try this:
=SUMPRODUCT(--(B1:B10="ATLANTA"),--(F1:F10="ACTIVE"))

Note that spelling matters! ATLANT A is different from ATLANTA and
even a trailing or leading blank space will skew your results. Hope
this does it for you!

 

[Guest]

Thanks! That worked perfectly.

One way

=SUMPRODUCT(--(B2:B100="ATLANTA"),--(F2:F100="ACTIVE"))

you could replace the criteria with cell references so it be easier to
change criteria, also note that unlike countif you can't use the whole column
like B;B

Regards,

Peo Sjoblom