#NO MATCH error on a Sheet Summary Formula

Mary Farmer

Hello,

I'm guessing the problem is because the field I'm referencing is a formula and not a value, but would appreciate suggestions on how to handle this.

I want a sheet summary formula to return how many Variances exist for each VarianceCategory, so I can use this information in a report.

I've listed the formulas that I'm attempting, but they return #NO MATCH.

Thanks!

Mary

Frank B.

Hi @Mary Farmer I tried this and it seemed to work on my summary:

=COUNTIFS(Variance:Variance, <>"0", VarianceCategory:VarianceCategory, "AMB/EDI")

I just added the "" around the 0, give it a try.

HTH!

Mary Farmer

Thanks @Frank B. but this didn't help. The formula works fine when I reference a column that isn't a formula. Perhaps I need to list out each 'responsible' separately in the COUNTIFS formula, instead of using the VarianceCategory...? Might that formula look something like the below in order to get a count of variances for the VarianceCategory of IS?

When I try the below, I get #INCORRECT ARGUMENT SET.

=COUNTIFS(Variance:Variance, <>0, Responsible:Responsible, "IS Network OPS", "RCM Engineering", "RCM IS")

Thoughts?

Frank B.

Hi @Mary Farmer how about we break it down to see what the issue is (I'm guessing it's the Variance value), try these separately and see if you get the expected results:

=COUNTIF(VarianceCategory:VarianceCategory, "AMB/EDI") should result in 6

=COUNTIF(Variance:Variance, <>"0") should result in 7

=COUNTIF(Variance:Variance, <>0) should result in 7

LMK!