Countifs arrays with multiple criteria and subtotal function

I think you have almost answered your own question. The COUNT(IF array formula and the COUNTIFS version will count all rows, visible or not. The SUMPRODUCT/SUBTOTAL version is the best way to count or sum with conditions whilst ignoring filtered out rows (unless you want to use a helper column)

Originally Posted by daddylonglegs

The SUMPRODUCT/SUBTOTAL version is the best way to count or sum with conditions whilst ignoring filtered out rows (unless you want to use a helper column)

I was thinking that the new AGGREGATE function (Excel 2010) would be a good alternative to the standard SUMPRODUCT/SUBTOTAL but I'll be darned if I can get it to work.

In this case the help file entry on the AGGREGATE function is downright pitiful.

Hey guys. Thank you so much for the quick response. OK. So, if I went with the the SUMPRODUCT/SUBTOTAL option, when I evaluate both array formulas the 1st one below is counting 164 items while the 2nd one is counting 141. Why is that? Which one is counting accurately when there is no filter on? Because I assume, both array formulas should at least have the same count result when the filter is not on for both of them, right? I realize one of them has the subtotal function and the other doesn't, but either way they should have the same result from a non filtered scope scenario.

I just want to make sure that if I go with Option 2. below that I'm not missing out on accuracy. I want to be able to explain why one is correct and the other isn't.

1. {(COUNT(IF(RngVal1>=$G$19,IF(RngVal1<=$G$18,1,0))))} = 164

2. SUMPRODUCT(SUBTOTAL(3,OFFSET(RngVal1,ROW(RngVal1)-MIN(ROW(RngVal1)),,1)),--(RngVal1>=$H$19),--(RngVal1<=$H$18)) = 141

141 is correct. In the COUNT formula COUNT counts any numbers, including zero so because the formula returns 1 for TRUE and zero for FALSE those are both numbers so it's counting all rows, if you change to this version

=COUNT(IF(RngVal1>=$G$19,IF(RngVal1<=$G$18,1)))

confirmed with CTRL+SHIFT+ENTER

then you'll get 141 as you will with the COUNTIFS function

I didn't look at your file...

In the COUNT(IF array formula remove the 0 at the end:

=COUNT(IF(RngVal1>=$G$19,IF(RngVal1<=$G$18,1)))

That will/should return the correct result when no filter is applied. It will not work correctly when the filter is applied. It does not exclude any hidden rows when the filter is applied.

We need to use the SUMPRODUCT/SUBTOTAL version to exclude hidden rows when the filter is applied.

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.