COUNTIFS when Referencing another Sheet

JAmirali

I am making a dashboard and have created a Calculation Sheet that will link to the Dashboard. All of my calculations reference other sheets. Most of the formulas have been simple thus far. All I have been doing is counting when a certain symbol shows up in a column using COUNTIF.

For a specific sheet, I have multiple business units I am tracking. Those show up in the form of numbers (ex 1200, 1300, etc). I want to count the symbols in the next columns but also break it up by business unit. There are two symbols to count for each unit.

Therefore, I need to calculate the following:

COUNTIFS Business Unit A in Column 1 shows a "!" ("high" symbol) in Column 2 OR when a flag is checked in Column 3

I have been seeing some threads that are answering similar questions but only when working within the same sheet. For this one, I will be referencing a separate sheet and do not know how to format this. Tried to ask the AI tool but it did not understand me.

Thank you!

Matt Lynn-PCG

@JAmirali just to make sure, your single formula/function set needs to pull from one sheet not multiple. Multiple sheets would be multiple formula/functions although it could be combined into one after they individually calculate.

That being said, a screenshot of your sheet(s)/columns would help, BUT you're on the right track with the countifs but you'll need two separate ones since you have two separate column ranges. The issue is you don't want to duplicate it if both are selected.

It should be something like:

=COUNTIFS([Column 1]:[Column 1], [Column 5]@row, [Column 2]:[Column 2], "high", [Column 3]:[Column 3], 0) + COUNTIFS([Column 1]:[Column 1], [Column 5]@row, [Column 3]:[Column 3], 1)

Adam Murphy

Maybe something like =COUNTIFS({Column 2}, "High") + COUNTIF({Column 3}, 1)

JAmirali

I was thinking that but the first criteria has to be the selected business unit and either or of the other options after that. So I'm a bit lost because it seems like it needs to include an AND / OR statement and I'm not sure how to write that and where to put the brackets or parenthesis

JAmirali

@Matt Lynn-PCG I saw that formula structure on other threads but was not sure how it would look with the other sheet referenced. Maybe it would just have the name of that reference sheet in place of the [Column 1]:[Column 1]?

Please see the below screenshot of part of my sheet. The first column is the business unit. There are several other business units on the same sheet. I am wanting to count the number of times both a ! or Flag appear which are the 2nd and 3rd column. I need to do this for each business unit in the sheet.

Screenshot 2024-08-20 150210.png

I hope this makes sense! I think we are close. For reference, my current formulas that just count how many flags, for example, is COUNTIF({Sheet Reference Name}, 1)

Matt Lynn-PCG

@JAmirali The formula I put above seems to work with that layout but there's a column where you'd list each business unit so the formula can REF that. See this image:

JAmirali

@Matt Lynn-PCG Hi again. I tried this formula but with referencing the sheet I gave a screenshot of and I'm still a bit confused.

Can you explain referencing column 5? The screenshot I provided was of the sheet I am taking the counts of, but the actual calculation is written on a separate sheet.

Matt Lynn-PCG

@JAmirali

So the or() wouldn't work here as the or function would need to go around two optional criteria of the same source column. Your or() situation would come from both the priority and the flag columns.

Look at this published example sheet here. Notice the value (answer) in column 1 of rows 2, 3 and 4 work, but wit the formula I provided above. Since the criteria are different I have to evaluate them separately but also account for possible duplicates. Row 5 shows how an OR here will not work. Row 6 shows how an OR would work, but not in the case you have outlined. This or is both blue and green coming from a single source column.

https://app.smartsheet.com/b/publish?EQBCT=c0de85434af64071abc75643de94588b

JAmirali

Hi Matt, I figured it out!

The solution was similar to the way you had it written but with 1 less column referenced.

Thank you for your help :)