Small VBA Macro: introduce cell text into a CountIF formular and get output

[Schutzhofer]

Hey Guys! I'm struggling with a problem and can't get over it.

I have a table that should only count if 2 values (Brand and Continent) are true in the same row and output the sum. (=COUNTIF(A2:A30;"SEAT";B2:B30;"Europe")
Now since that would mean I have to edit the formula every time I want to filter for a Brand (or later with more arguments), I would like to put it into a Macro (but didn't get it to work).
The goal is to only enter the name of the Brand somewhere in column E and it will automatically filter for it and give you the sum adjacent next to it (see picture).

Sorry for the inconvenience if this is a very basic problem. :-(

111111.jpg

[p45cal]

I have to edit the formula every time I want to filter for a Brand (or later with more arguments)

Would a pivot table and slicers do?

2023-01-18_211928.png

[Schutzhofer]

Would a pivot table and slicers do?

Well I have thought about it yes.
Sorry I forgot to mention, that the (data) table with all the Brands and Continents can and will be extended with more rows and new Brands.
For that the calculation has to be live and preferably no big buttons or other elements. For that a PivotTable might not be very suited, that's why I tried to Code in VBA.

Here is the possible behavior of the table. In contrast to the last picture the following has changed:

• The last BMW changed to Europe so the counter jumped to 3 (was 2 before)

• Another Tesla has been found and entered into the table data (still 0)

• And I'm searching for a new Brand the Ford, but got no Data yet.

My apologies.

22222.jpg

[p45cal]

If the source table is a proper Excel table, with or without other headers, continents, countries, the pivot will cope with it automatically since such a table resizes itself.
To avoid buttons etc, a few lines of vba will refresh the pivot when the source data changes.
The slicers will allow you to choose what to see.
If you add a new Brand (or continent) the pivot and slicers will immediately show them.

[Schutzhofer]

If the source table is a proper Excel table, with or without other headers, continents, countries, the pivot will cope with it automatically since such a table resizes itself.
To avoid buttons etc, a few lines of vba will refresh the pivot when the source data changes.
The slicers will allow you to choose what to see.
If you add a new Brand (or continent) the pivot and slicers will immediately show them.

Wow that's awesome!
Last Question, how would it be possible if two Brands are in the same cell? Now I still have to manually pull them apart, but sometimes they come together like "Ford Audi". Can you somehow count both in the same cell?

For everything else already, thank you very much!

[p45cal]

Depends how often you have to do this. It won't be straightforward to split brands because some brands, eg. Land Rover also come as 2 words but you don't want that split!