Figuring out total counts of C based on A's relation to B.

[LeSteve]

I'm struggling to figure out exactly what it is I'm trying to do - so apologies if this is a garbled mess
I'm trying to get a count of something depending on how many times its referenced with another value.
I've made a mock up similar to what I'm trying to do:

Due to the way I'm getting the data, the items on sheet 1 and 2 are separate, but I'm trying to get a count of how many cars would have metallic paint?
I know in the example it should be 3 but I cant seem to get it to add up using countifs or sumifs.

 

[felixstraube]

Would something like this work?:

=LET(
vehicle, A2:A17,
paintColor, B2:B17,
paintFinish, XLOOKUP(paintColor, Sheet2!A2:A5, Sheet2!B2:B5, 0),
IFERROR(ROWS(FILTER(vehicle, (vehicle="Car")*(paintFinish="Metallic"))), 0)
)

or

=LET(vehicle, A2:A17,
paintColor, B2:B17,
paintFinish, XLOOKUP(paintColor, Sheet2!A2:A5, Sheet2!B2:B5, 0),
SUM((vehicle="Car")*(paintFinish="Metallic"))
)

 

[LeSteve]

I used the second one, it works! Thank you so much!

 

[felixstraube]

Thanks for the feedback. Happy to help.