Sumifs with visible rows after filter

[johnny_p]

Hi all

I need some help with a formula to only show me the values if the rows are visible after filter.

Looking for

If DZ7:DZ100000 <> "" then count how many "Yes" values within R7:R100000

I managed to cobble this together from the net to help me with another query but could not amend to solve the issue above.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Data!R7:R100000,ROW(Data!R7:R100000)-MIN(ROW(Data!R7:R100000)),,1)),ISNUMBER(SEARCH("Yes",Data!R7:R100000))+0)

Thanks for any help

[samba_ravi]

please attach sample excel file with desired result

[johnny_p]

Sorry I cant. Its on a work machine and cant upload or email out as it is monitored.

[Tony Valko]

What type of data is in column DZ? Is it text? Numeric? Could be both? Something else?

[samba_ravi]

pls attach a sample excel file with out sensitive data

[johnny_p]

Column DZ will have date formats

[Tony Valko]

Try this...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Data!DZ7,ROW(Data!DZ7:DZ100000)-ROW(Data!DZ7),0)),--(Data!R7:R100000="Yes"))

[johnny_p]

Great, Thanks for this it worked a treat.

How would I amend this if column DZ needed to equal "yes" instead

[Tony Valko]

Like this...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Data!DZ7,ROW(Data!DZ7:DZ100000)-ROW(Data!DZ7),0)),--(Data!DZ7:DZ100000="Yes"))

[johnny_p]

Simple- thanks

As you done a Stirling job with this any chance you could help out with a similar issue with Subtotal here -

[johnny_p]

Simple- thanks

As you done a Stirling job with this any chance you could help out with a similar issue with Subtotal here -

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.