COUNTIF "text" or "text" with and index match

[VKM]

Hi all,

I'm struggling to combine two formulas. What I am trying to do is COUNT the number times 'text_1' and 'text_2' appears in a column IF cell_a MATCH cell_b; however, the column reference for the INDEX needs to be based off another cell.

This COUNTIF INDEX MATCH works a treat:
=COUNTIFS(INDEX(PASTE!A:J,0,MATCH('RR%'!N6,PASTE!$A$1:$J$1,0)),'RR%'!K6)

And the following formula works, but I have to manually edit the column reference by filtering by working out which column needs to be referenced:
=SUM(COUNTIFS(PASTE!$B:B,K6,PASTE!$W:$W,{"Completed (31)","Completed after break (32)"}))

I somehow need to combine these to follow this logic:

1. If text in cell_a equals column header, use this column, AND
2. If contents of the cells in the column identified above equals cell_b, THEN
3. Count the number of times 'text_1' and 'text_2' appears in another column

Any help would be massively appreciated.

Tx. V

 

[Fluff]

Not entirely sure what you want, but maybe

=SUM(COUNTIFS(INDEX(PASTE!A:J,0,MATCH(N6,PASTE!$A$1:$J$1,0)),K6,PASTE!$W:$W,{"Completed (31)","Completed after break (32)"}))

 

[VKM]

Not entirely sure what you want, but maybe

=SUM(COUNTIFS(INDEX(PASTE!A:J,0,MATCH(N6,PASTE!$A$1:$J$1,0)),K6,PASTE!$W:$W,{"Completed (31)","Completed after break (32)"}))

Hi @Fluff - you absolute legend! ?

This works perfectly. I Just couldn't get my head around the structure without returning a formula error. Your solution has helped educate me as I will be using this a lot.

Thanks so much!
V

 

[Fluff]

Glad to help & thanks for the feedback.