Ignoring hidden values in complex sumproduct formula

[fadetograham]

Hi guys

Got a couple of sumproduct formulas, which I now need to ignore hidden values in all columns contained within the formula when another column is filtered.

=SUMPRODUCT(((M5:M667="Yes")*(P5:P667="Yes"))+((T5:T667="Yes")*(V5:V667="Yes")))

&

=SUMPRODUCT(((M5:M667="Yes")*(P5:P667="No")*(T5:T667="No")*(NOT(V5:V667="Yes"))*(NOT(V5:V667="Awaiting Response"))+(V5:V667="No")))

I've managed it for a more simple formula using a tutorial I found on the use of sumproduct subtotal offset but only looking for 1 value/block of text but can't seem to integrate for these more complex ones.

If anyone can help or point me in the direction of a more advanced tutorial would really appreciate it.

Thanks
Graham

 

[Jonmo1]

I suggest a helper column in say column Z for example.
Z5: =SUBTOTAL(103,M5)
And filled down to Z667

Then add a segment to each formula as
*(Z5:Z667)

 

[fadetograham]

Hi Jonmo1

Thanks for your response and idea of a helper column; has given me a different train of thought to hop on.

Not sure your formula works in this situation as it gives a value of 1 wherever the the cell contains a value, which it does in all instances being either Yes, No or Awaiting Response.

But the helper column idea worked a treat where I was trying to combine counts for 2 columns where I couldn't combine in the formula.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(P5:P667,ROW(P5:P667)-ROW(P5),0,1)),--(P5:P667="Awaiting Response"))

&

=SUMPRODUCT(SUBTOTAL(3,OFFSET(V5:V667,ROW(V5:V667)-ROW(V5),0,1)),--(V5:V667="Awaiting Response"))

Got put in AB4 & AC4 respectively then those cells added together in the display cell, which solved that one but the original formuals I posted would require a lot of additional columns and was looking for a one cell fits all solution.

What would be great is if excel had a "ignore if hidden" function which could be easily applied for all formula. Like $ is used for absolute, £ around a function term could be understood as ignore hidden values.

Thanks again for the help, really appreciate it

 

[Jonmo1]

Not sure your formula works in this situation as it gives a value of 1 wherever the the cell contains a value, which it does in all instances being either Yes, No or Awaiting Response.

Only if the cell is Visible
It will be a 0 if the cell is Not visible (hidden).
Causing the product of that row to be 0.

 

[Jonmo1]

I did notice the formulas you posted are doing OR criterias.
((criteria1)+(criteria2))

So it may be tricky in the placement of the *(Z5:Z667)
It must go outside the OR syntax

Incorrect
((criteria1)+(criteria2)*(Z5:Z667))

Correct
((criteria1)+(criteria2))*(Z5:Z667)

 

[fadetograham]

Hey Jonmo1

Sorry for the delayed response but managed to get it all sorted.

Thanks again

 

[Jonmo1]

You're welcome.