How do I only count visible rows when using the COUNTIFS ,COUNTIF, and SUMIF formulas-CodePudding

I've been using Excel's COUNTIFS function to count the number of rows in a table, but I'm not sure how to change my formulas to only count from my tables when using filters.

Here are the formulas that I am currently using.

=COUNTIF(USSW1!V7:V10000,110) COUNTIF(USSW2!V7:V10000,110)-AB6

= SUMIF(USSW1!$V7:$V10000,"110",USSW1!O7:O10000)-SUMIFS(USSW1!O7:O10000,USSW1!M7:M10000,"FP",USSW1!V7:V10000,"110")

=COUNTIFS(USSW1!V7:V10000,"109",USSW1!M7:M10000,"FP") COUNTIFS(USSW2!V7:V10000,"109",USSW2!M7:M10000,"FP")

= SUMIF(USSW1!$V7:$V10000,"110",USSW1!O7:O10000)-SUMIFS(USSW1!O7:O10000,USSW1!M7:M10000,"CA",USSW1!V7:V10000,"110")-SUMIFS(USSW1!O7:O10000,USSW1!M7:M10000,"CF",USSW1!V7:V10000,"110")

=SUMIF(USSW1!$V7:$V10000,"110",USSW1!O7:O10000)

CodePudding user response：

You need to switch to SUMPRODUCT in such cases. An example would be:

=SUMPRODUCT(N(MyCol1="X"),N(MyCol2="Y"),SUBTOTAL(3,OFFSET(INDEX(MyCol1,1),ROW(MyCol1)-MIN(ROW(MyCol1)),)))

which is counting how many visible rows there are for which the entry in MyCol1 is "X" and the entry in MyCol2 is "Y".