Search code examples
google-sheets

How to count 2 sets of data in one column in google sheets

I am trying to count 2 sets of data that appear in one column in google sheets. One set is the base domain and the other set is the base domain with .com on the end. It is always .com. Here is an example: https://docs.google.com/spreadsheets/d/1MCfaHboltvzVRtznxG7u8nChaVAkVdfv2jN8cjvtJCk/edit#gid=680355622

This formula works for one set of data. =arrayformula({"Results";IF(COUNTIF(A2:A,{B2:B})>0,"Yes","")})

However, if I add &".com" to a copy of the same formula only using the countif twice it doesnt work.

Ideally I'd like to have the header in this row because I dont know how many matches there will be and I do this daily and have a tab for the different months.

Solution

  • Slightly amended your current formula. Please do test it out:

    =arrayformula({"Results";if(len(B2:B),if(countif(A2:A,substitute(B2:B,".com",))>0,"Yes",""),)})