Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 115
  • Last Modified:

Countifs formula

I think I understand this formula as I did not input into the sheet.  I am manually looking at the results and they do not match what the formula below returns.  I am assuming it is looking for the Values from division info in QQ where the values in A match the values in EE on the division tab, but it does not see to work.


=COUNTIFS('division info'!E:E,'OCT'!A28,'division info'!Q:Q,">0")
0
leezac
Asked:
leezac
  • 2
1 Solution
 
byundtCommented:
The formula is returning the count of rows where column E matches A28 and column Q is greater than 0. If you were looking for  a value from division info column QQ, then a different formula is required.

Are you getting a smaller count than you expected? If so, you might have trailing spaces (either in division info column E or OCT cell A28) that cause the match to fail.

Could you please post a sample workbook that illustrates the problem?
0
 
leezacAuthor Commented:
I think it was my mistake - I had another column filtered.   But I also wanted to make sure I was seeing the formula correctly.
0
 
byundtCommented:
I think it was my mistake - I had another column filtered.
Be aware that COUNTIFS will count rows even if they have been hidden by a filter. If you want to ignore rows hidden by a filter, you should use either SUBTOTAL or AGGREGATE to do the counting with an array-entered formula. AGGREGATE has a little more power in that you can also ignore rows containing an error value, but it requires Excel 2010 or later. SUBTOTAL has no such restriction. Please post back if you need help building such a formula.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now