CountIf Formula Help across sheets

tracy.heppes40761

I have used this formula in previous sheets but seem to be having some difficulties in pulling the correct data in this new sheet.

Current Formula:

=COUNTIFS({MazdaPortfolioSummary.EnrollmentRecd}, IFERROR(YEAR(@cell ), 0) > 2015, {MazdaPortfolioSummary.Region}, Midwest1)

We are setting up control center and only have one project provisioned, but it should be reading at least 1.

Any thoughts on why this isn't pulling the criteria i'm searching

Paul Newcome

Ok. So we know that we are not getting a match on the year portion. Check to make sure the column being reference by that particular range is set as a date type column. If it already is, how exactly is it being populated?

heyjay

Wrap string criteria in quotes.

=COUNTIFS(
{MazdaPortfolioSummary.EnrollmentRecd}, IFERROR(YEAR(@cell ), 0) > 2015, 
{MazdaPortfolioSummary.Region}, "Midwest1")

tracy.heppes40761

That isn't the nomenclature in the source field, it's just Midwest. Which when I update it to the "Midwest" it still doesn't work.

Paul Newcome

@heyjay Midwest1 is a cell reference.

@tracy.heppes40761 What happens if you (temporarily for troubleshooting) break it into two separate COUNTIFs? One for the year and one for the region? Do they both still show zero, or does only one of them show zero?

tracy.heppes40761

So, I should preface this is with using Control Center and connecting a lot of datapoints that way. However, if I just reduce the formula to : =COUNTIF({MazdaPortfolioSummary.EnrollmentRecd}, IFERROR(YEAR(@cell ), 0) > 2015) , It still doesn't pull any info.

Paul Newcome

Ok. So we know that we are not getting a match on the year portion. Check to make sure the column being reference by that particular range is set as a date type column. If it already is, how exactly is it being populated?

tracy.heppes40761

oh my gosh, you are a genius. it turns out none of those are date columns, which is why it wasn't pulling data…..i guess when you look at a sheet/problem for so long you don't see ALL of those details. doh!

Paul Newcome

Happy to help. 👍️