Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 111
  • 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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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