Sumproduct

[charli chali]

Hey,

I have this fantastic sumproduct formula that works very well.
=SUMPRODUCT(0+ISTEXT(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",1,$B6:$B10,$B$12:$B$16,$B$18:$B$22),",","</b><b>")&"</b></a>","//b[not(preceding::*=.)]")))

However, I would like to add condition, that count only if the status in column c "tick" meaning yes. If not, don't count.

How do I do that?

 

[Fluff]

Hi & welcome to MrExcel.
How about

=SUMPRODUCT(0+ISTEXT(FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,B6:B22&C6:C22)&"</m></k>","//m[not(preceding::*=.) and contains(.,'"&UNICHAR(10003)&"')]")))

 

[Fluff]

You could also use

=SUM(IF(ISTEXT(B6:B22)*ISTEXT(C6:C22),1/COUNTIFS(B6:B22,B6:B22,C6:C22,UNICHAR(10003)),""))

This will need to be confirmed with Ctrl Shift Enter, rather than just Enter

 

[charli chali]

Hi & welcome to MrExcel.
How about

=SUMPRODUCT(0+ISTEXT(FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,B6:B22&C6:C22)&"</m></k>","//m[not(preceding::*=.) and contains(.,'"&UNICHAR(10003)&"')]")))

Hi and thanks alot for your kind response. This one returns answer as = 0. See the screenshot below.

 

[charli chali]

You could also use

=SUM(IF(ISTEXT(B6:B22)*ISTEXT(C6:C22),1/COUNTIFS(B6:B22,B6:B22,C6:C22,UNICHAR(10003)),""))

This will need to be confirmed with Ctrl Shift Enter, rather than just Enter

This is very PERFECT. Thank you very much.

 

[Fluff]

You're welcome & thanks for the feedback.