SOLVED

# 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

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 X15

13 Replies

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

=SUM(COUNTIF(B2:F2,V2:W2))

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.

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

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

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

Also i am unable to then use this to try and add the X3 column as i tried adding X2 to the formula

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

=SUM(COUNTIF(B2:F80,V2:X2))

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

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

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

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

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.

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

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

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

See attached spreadsheet with my suggestion.

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

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)
Solution

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

See attached file.

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

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

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

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

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

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?