Help with COUNTIFS function

Eric Hall

I am trying to create a COUNTIFS formula to populate a dashboard and can't seem to get it quite right. I am sure it is a very minor issue but was hoping for some help.

In the sheet below, I want to create a formula on my data sheet to match the "related product" and the qtr in the "qtr due" column. For example, I want to know how many Envarsus entries are due in Q1 FY25

Again, I want to create this is a separate sheet to populate my dashboard, unless there is a way to do it directly from this sheet which I have not tried before.

Elska19

Hi Eric,

To set up the formula in another sheet it would look something like the below equation:

=COUNTIFS({Range Related Product},"Envarsus",{Range Qtr Due}, "Q1 FY25")

This formula would give the count of all rows that have Envarsus in the Related Product column and Q1 FY25 in the Qtr Due column. When you're in the other sheet entering the column reference, choose Reference Another Sheet and then navigate to this sheet and choose the appropriate column.

If the two columns you're using in the Count Ifs function are not dropdowns that have standardized entries but instead might have the text you're looking for you could do this:

=COUNTIFS({Range Related Product},CONTAINS("Envarsus",@cell),{Range Qtr Due}, CONTAINS("Q1 FY25",@cell))

Hope this helps!

Elska19

Hi Eric,

To set up the formula in another sheet it would look something like the below equation:

=COUNTIFS({Range Related Product},"Envarsus",{Range Qtr Due}, "Q1 FY25")

This formula would give the count of all rows that have Envarsus in the Related Product column and Q1 FY25 in the Qtr Due column. When you're in the other sheet entering the column reference, choose Reference Another Sheet and then navigate to this sheet and choose the appropriate column.

If the two columns you're using in the Count Ifs function are not dropdowns that have standardized entries but instead might have the text you're looking for you could do this:

=COUNTIFS({Range Related Product},CONTAINS("Envarsus",@cell),{Range Qtr Due}, CONTAINS("Q1 FY25",@cell))

Hope this helps!

Eric Hall

Thanks! That is exactly the formula I tried but it comes back as unparseable. For whatever reason it is also showing the references as two different titles even though they are in the same sheet. I just substituted another product and qtr due from the sheet since I am trying to do this across multiple products and multiple qtrs. Would another formula be better for this application?

Elska19

From the image above I'm seeing a missing " after the second criteria (Q4 FY23), so that can prevent the formula from working.

Also, it's hard to tell the sheets the formula is referencing but all range references have to come from the same sheet to work, whether that's the sheet you're in or referencing another one. You can rename the sheet references but the defaults are always {Sheet Name Range 1}, {Sheet Name Range 2}, etc. Based on your picture it looks like it may be two different sheets (Submission Calendar and Submission Calendar Mock Up) which could also pose a problem but it's hard to know for certain without being in the sheet and reviewing the formula.

Eric Hall

Thanks. The ranges are in the same sheet so not sure why the formula reference is showing two different names. I tried to recreate the table from scratch, and it seems to be working with the formula you provided. Your help is much appreciated