I have two Google sheets with different Fictional characters (column A) and their corresponding Type (column B).
Sheet1
| Fictional character | Type |
|:--------------------|:---------------:|
| Spider Ham | Pig |
| Iron Man | Human |
| Captain America | Human |
| Thor | God |
| Hercules | God |
| Superman | Alien |
Sheet2
| Fictional character | Type |
|:--------------------|:---------------:|
| Spider Ham | Human |
| Iron Man | Human |
| Captain America | Human |
| Thor | God |
| Hercules | Demi-God |
| Doctor Octopus | Human |
The differences between Sheet1 and Sheet2:
- Sheet1 have the fictional character "Superman", which Sheet2 does not have
- Sheet2 have the fictional character "Doctor Octopus", which Sheet1 does not have
- The fictional character "Spider Ham" and "Hercules" have another type in both sheets
I want to compare how many of the fictional characters that have a different Type (excluding characters that does not exist in both sheets)
Sheet3 should look like this:
| Difference | Sum |
|:--------------------|----------------:|
| Same type | 3 |
| Not the same type | 2 |
I have tried with the following, but it's not a valid formula - and I haven't figured out how to exclude if characters that does not exist in both sheets:
=COUNTIF(VLOOKUP(Sheet1!A:A;Sheet1!A:B;2;FALSE)=VLOOKUP(Sheet2!A:A;Sheet1!A:B;2;FALSE);"=TRUE")
=COUNTIF(VLOOKUP(Sheet1!A:A;Sheet1!A:B;2;FALSE)=VLOOKUP(Sheet2!A:A;Sheet1!A:B;2;FALSE);"=FALSE")