I need help with a formula in my Summary Sheet

Kitty

This is what I have:

=SUM(COLLECT([Country Count]:[Country Count], [Location Status]:[Location Status], "Active"), COLLECT([Country Count]:[Country Count], [Location Status]:[Location Status], "Operational"))

Because we have work in several locations of a country, I only need to count the country once. I have that formula in place.

But there are 2 Location Status choices that I want to collect the sum of. One choice is "Active" and the other choice is "Operational". So if there are 3 active locations and 2 operational locations, I want the sum to show 5.

Thank you for ANY help!

Kitty Sandel

Kitty

YESSSSSSS! You are a genius!

I cannot thank you enough for your help!!

Have a blessed day!

Kitty

Genevieve P.

Hi @Kitty

You can actually use a SUMIF formula for this!

Try:

=SUMIF([Location Status]:[Location Status], OR(@cell = "Active", @cell = "Operational"), [Country Count]:[Country Count])

However, this will SUM together the numbers found in the "Country Count" column. So in your example, you say "if there are 3 active locations and 2 operational locations, I want the sum to show 5".

This formula will find the 3 "active" locations, but then if they each have the number 5 in the Country Count column, it will return 15, summing the numbers. Does that make sense?

If you're just looking to COUNT the rows that contain either "Active" or "Operational", you could use this:

=COUNTIF([Location Status]:[Location Status], OR(@cell = "Active", @cell = "Operational"))

If I've misunderstood what you're looking to do, it would be helpful to see a screen capture of your sheet but please block out any sensitive data.

Cheers!

Genevieve

Kitty

I'm sorry I wasn't clearer in my question. The formula you gave me worked, but it collected Active Locations. I'm looking to collect, in this instance, Active Countries. We have several locations in one country, so I don't want to count that country each time.

On the screen to the right, you'll notice there are 4 locations in the United States, but I only want to count the one Country of the "Active" locations. For instance, if Texas, Houston - Northwest were "Active", then that shows 2 "Active" locations and with the formula you gave me, it gives me two "Active" countries.

Thank you SO VERY MUCH for working with me on this! It's for a Board meeting this Thursday. I did a work-around for the PDF of the Dashboard and just cleared the info to feed that information and entered it manually. But it's very important to get it up and running by Thursday. :)

Genevieve P.

Hi @Kitty

Thank you for clarifying! This definitely helps. I can see in your screen capture that you have a column identifying the country, which is great. In this instance a COUNT(DISTINCT(COLLECT formula would be a good way to go.

The COLLECT function will be your filter, to filter down the Country column based on if the Location Status is either Active or Operational. Then the DISTINCT will only list each country that meets the Collect criteria once, and your COUNT will count how many unique countries there are.

Try this:

=COUNT(DISTINCT(COLLECT([CHARTERED LOCATION COUNTRY]:[CHARTERED LOCATION COUNTRY], [Location Status]:[Location Status], OR(@cell = "Active", @cell = "Operational")))

Let me know if this is what you were looking for!

Cheers,

Genevieve

Kitty

YESSSSSSS! You are a genius!

I cannot thank you enough for your help!!

Have a blessed day!

Kitty

Genevieve P.

No problem at all! I'm glad I could help. 🙂