Paste formulas not working

Excelling5 

It truly is hard to give specific help without seeing the specific layout(s) of your workbook(s).

I've used the various COUNT functions recently with the FILTER function nested in it to select the rows to be counted. The latter is very new and requires the most recent version of Excel, but if you have it, it can be far more powerful than VLOOKUP. You could also try XLOOKUP.

Here's a reference that explains FILTER and a couple of other equally powerful and complementary functions. https://www.youtube.com/watch?v=9I9DtFOVPIg

By the way, your reference to multiple tabs concerns me. There are many times when we see posted here sample files where multiple tabs are used to represent, say, different customers or vendors, and the products associated. Excel works VERY well--you might say it excels--when such data, albeit for different customers or vendors or products, are ALL arrayed on a single sheet, in a single table, with columnar identifiers used to designate the customer or vendor or product (whatever it was that supposedly warranted its own sheet).

So that too could be part of a solution: consider consolidating those multiple tabs into a single database. You might be astounded at how much simpler you've made your task when you let Excel do the "heavy lifting" rather than trying to "help" by separating those entities out.