Excel formulas
 

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

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

"jbressma" wrote:

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

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!

Thanks! That worked perfectly.

"Peo Sjoblom" wrote:

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

"jbressma" wrote:

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