2 Basic Questions: Counting items in a named range & excluding items in the count

[Burrgogi]

I've got a list of game DLCs in column A - goes from A2 through A500. I've created named ranges for the vast majority of items in the list; there are however approx. 30 or 40 items that do not have a name. At the bottom of the sheet, I've got a summary. Let's say in cell B510 I have the following:
=COUNTA(A2:A500)

It gives me a total count of the items (good) but how can I make it exclude the items from the total count that DO NOT have a named range?

Second part of the question:

In the summary section just below the overall total count shown, I'd like to have another statistic displayed. I'd like to have a total count of all the DLCs excluding the items that are notated with "BGM" in column C.
In short, I'd like for cell B511 to read as follows: ="Effective Count Total: xxx". Where xxx = the number displayed in cell B510 minus yyy. Where yyy is the total number of items notated with BGM in column C.

 

[Rijnsent]

Hi there,
so for question 1: that's quite hard - I don't know what "have a named range" means: a cell can be in various named ranges, it can be a named range on its own or being part of a bigger named range. Having said that: there is no formula I know of that can tell you if a cell is (part of) a named range. You could probably solve it with VBA, but you'd have to loop over all named ranges.
For your second question, you could use the COUNTIFS formula, something like =COUNTIFS(A5:A500,"<>",C5:C500,"<>BGM").
Cheers,
Koen