I am trying to make the Cell W15 Calculate when the words in V2 and W2 appear in the same row

Occasional Contributor

I am trying to make the Cell W15 Calculate when the words in V2 and W2 appear in the same row between Column B and F and then do the same with V2, W2, X2 but for X15Capture 2.PNG

13 Replies



Enter formula as arrayformula with ctrl+shift+enter if you don't work with office365 or 2021. 

Is this what you are looking for? Please compare attached file.

Thankyou for the reply!
Not quite as when i plugged it into my own spreadsheet, it will only count the one row so i changed the F2 to F80 as there are 80 rows but then it seems to be counting one extra row which i think is V2:W2 so i just added a -1 at the end of the function
Also i am unable to then use this to try and add the X3 column as i tried adding X2 to the formula




This formula counts number of all words of cells V2:X2 that appear in range B2:F80 in my spreadsheet.


Can you communicate the formula you entered or can you upload your spreadsheet? This would help trying to solve your request.

@Quadruple_Pawn  I attached my spreadsheet. Seems to be only identifying when a row has 1 of the variables rather than all of them


Which Office version do you work with? If you don't work with Office365 or 2021 you have to enter arrayformulas with ctrl+shift+enter. After doing this formulas return 5, 15 and 22.

I am Using Office 365. The Value should be that for every time it finds the 2 or the 3 values it counts as 1. So W15 should be 5 as Landy and Carmina re in the same row 5 times and X15 should be 3 as Landy Carmin and Kise are in the same row 3 times


See attached spreadsheet with my suggestion.

It works but is there anyway i can make the names changable since i tested it and it only works with the names Kise Carmin etc but not when i change it to the cell name
best response confirmed by Keyamo (Occasional Contributor)


See attached file.

Thanks alot ill try and mess around with it see if i can work up the 3 Unit combo formula. Appreciate the help alot.


Glad it helped. I forgot the 3 unit combo formula.

I managed to figure that one out which is nice shows im learning a bit. I dont wanna be a bother but i posted another discussion about how i wanted it to calculate how often i win with different combos. Do you think this would be possible?