Change SUMIF and CHANGIF formulas so that filtered data only gets calculated.

[bored622]

Hello, I currently have these formulas that calculate data from another tab's table, but if I filter data out of the table, my formulas still calculate the filtered out data. I'm pretty new to excel, but I've read a few forums on using SUMPRODUCT to get the results I want but not sure how to do it. Below I have posted a few formulas. If anyone can help, it would be greatly appreciated.

=COUNTIF(USSW1!$V$7:$V$25000,465)

=SUMIF(USSW1!$V$7:$V$25000,"465",USSW1!$O$7:$O$25000)-SUMIFS(USSW1!$O$7:$O$25000,USSW1!$M$7:$M$25000,"FP",USSW1!$V$7:$V$25000,"465")

=COUNTIFS(USSW1!$V$7:$V$25000,"465",USSW1!$M$7:$M$25000,"FP")

=SUMIF(USSW1!$V$7:$V$25000,"465",USSW1!$O$7:$O$25000)-SUMIFS(USSW1!$O$7:$O$25000,USSW1!$M$7:$M$25000,"CA",USSW1!$V$7:$V$25000,"465")-SUMIFS(USSW1!$O$7:$O$25000,USSW1!$M$7:$M$25000,"CF",USSW1!$V$7:$V$25000,"465")

=SUMIF(USSW1!$V$7:$V$25000,"465",USSW1!$O$7:$O$25000)

 

[bebo021999]

Have you tried SUBTOTAL or AGGREGATE?

 

[Fluff]

Hi & welcome to MrExcel.
I would add a helper column to the USSW1 sheet with this formula

=SUBTOTAL(3,A7)

change the A7 to a column that will always have data.
Then you can use you existing formulae with a minor change.

=SUMIFS(USSW1!$O$7:$O$25000,USSW1!$V$7:$V$25000,"465",USSW1!$Z$7:$Z$25000,1)

=COUNTIFS(USSW1!$V$7:$V$25000,465,USSW1!$Z$7:$Z$25000,1)

changing the Z column to the one with the subtotal formula.
And similar changes to your other formulae

 

[bored622]

Hi & welcome to MrExcel.
I would add a helper column to the USSW1 sheet with this formula

=SUBTOTAL(3,A7)

change the A7 to a column that will always have data.
Then you can use you existing formulae with a minor change.

=SUMIFS(USSW1!$O$7:$O$25000,USSW1!$V$7:$V$25000,"465",USSW1!$Z$7:$Z$25000,1)

=COUNTIFS(USSW1!$V$7:$V$25000,465,USSW1!$Z$7:$Z$25000,1)

changing the Z column to the one with the subtotal formula.
And similar changes to your other formulae

I went back and changed all my formulas to sumproducts. Would there be a way to exclude filtered data using these formulas?

=SUMPRODUCT(--(USSW1!$V$7:$V$50000="465"))

=SUMPRODUCT(--(USSW1!$V$7:$V$10000="465"),--(USSW1!$O$7:$O$10000),--(USSW1!$M$7:$M$10000="CA")--(USSW1!$M$7:$M$10000="CF"))

=SUMPRODUCT(--(USSW1!$M$7:$M$5016="FP"),--(USSW1!$V$7:$V$5016="465"))

=SUMPRODUCT(--(USSW1!$V$7:$V$10000="465"),--(USSW1!$M$7:$M$10000="FP"),--(USSW1!$O$7:$O$10000))

=SUMPRODUCT(--(USSW1!V7:V5016="465"),--(USSW1!O7:O5016))

 

[Fluff]

Did you try what I suggested?

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to exclude filtered cells from table when using SUMPRODUCT
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[bored622]

Did you try what I suggested?

Yea but I got the error "too many arguments".

 

[Fluff]

Did you copy/paste the formulae I suggested, or modify your own?

 

[bored622]

Did you copy/paste the formulae I suggested, or modify your own?

Yeah, but these two formulas are giving me issues whenever I try to input the extra column.

=SUMIF(USSW1!$V$7:$V$25000,"465",USSW1!$O$7:$O$25000)-SUMIFS(USSW1!$O$7:$O$25000,USSW1!$M$7:$M$25000,"FP",USSW1!$V$7:$V$25000,"465")-SUMIFS(USSW1!$O$7:$O$25000,USSW1!$M$7:$M$25000,"BK",USSW1!$V$7:$V$25000,"465")

=SUMIF(USSW1!$V$7:$V$25000,"465",USSW1!$O$7:$O$25000)-SUMIFS(USSW1!$O$7:$O$25000,USSW1!$M$7:$M$25000,"CA",USSW1!$V$7:$V$25000,"465")-SUMIFS(USSW1!$O$7:$O$25000,USSW1!$M$7:$M$25000,"CF",USSW1!$V$7:$V$25000,"465")

 

[Fluff]

You will need to change the SUMIF to SUMIFS as SUMIF does not allow more than one criteria.