Help with CountIFs formula

EricaJordan

Hello,

I am trying to use the CountIFs formula to calculate the number of times each category in a drop down list appears in one column. I'd also like to be able to make sure the items are only being counted are in Hierarchy level 2. Below is the list of categories, can anyone please help me understand writing the formula? I will be referencing another sheet for this formula as well, I'm writing the formula on my metrics sheet and the categories are on my main project tracking sheet.

State VP

Plural

FiscalNote

TechNet

IAPP

Other

juliesilverio

Hi Erica,

I would add a helper column into your main project tracking sheet and call it "Level" -- This is what we do at my organization to assign a number to each level in the hierarchy of a sheet and we can then use that number to include or exclude rows as needed. You can hide this helper column after adding it to keep your sheet streamlined.

The formula to assign a number to your rows is =COUNT(ANCESTORS())

Then you can write a formula in your metrics sheet to reference your main project tracking sheet (I used "Category" as the column header for example, but that is the portion of your formula where you will want to reference another sheet and select the relevant column) for each category, as below

=COUNTIFS(Level@row, 2, Category@row, "FiscalNote") - this formula will calculate the FiscalNote category at Level 2 in your sheet.

To count the other categories, you will just want to change the formula and replace "FiscalNote" with "IAPP" for example. I am assuming you want to count each category in a separate cell in your metrics sheet, but let me know if that assumption is incorrect.

Hope that helps!