countif over multiple ranges?

I have a column range "data", and two tables (one for each area), and a named range of the cost row of each area;
So I then have two named ranges; "data", and "cost", where "cost" name looks like this: =12:12,18:18

Now I want to do some computations using countif and/or sumif, like this:
countif(data cost, [criteria])
and
sumif(data dates, [criteria], data cost)

(Similarly, "dates" are rows in each area)

Both of these fail, but if I change to use the same functions just over each row intersection with "data" separately, it works fine.

Thus, it seems like countif/sumif fail with a range which evaluates to multiple disjoint areas - yes?

But it is cumbersome (messy) to have to write multiple copies of a formula(s) for each product, instead of just having a name which indicates the cost rows for all of them.

Sorry - mistake in attaching - updated now.

I don't know how you'd get it to work without the numbered ranges, but this should work with them:

=SUM(SUMIF(INDIRECT("days"&{1,2}),"<8",INDIRECT("cost"&{1,2})))

@Glen Kennedy: no; the answer is =280 (for your data; you changed the data from mine); the cost total for all days less than day 8 (in the real case, the day is a variable).

There are lots of ways to do it, I gave a few - but the question is about the countif function; it's definition is that it works over ranges, but this example seems to show that it will not work over any range with disjoint parts.

this is just a simple example to show this intended usage of countif, the question is about the function. I have seen similar undocumented range limitations on other excel functions, e.g. sumif seems to have a similar undocumented limitation.

@rory1; Thanks, neat solution, but my overall goal was to have one place where I could add references to new tables with data; and other formulae (this is just one example) would all pickup the new data just from expanding the "days" & "cost" names.

Your example is very nice, but I would have to locate every formula doing arithmetic over the tables and change all of them. So, so far it seems like the answer to my original question - can countif (and similar functions) work over named disjoint ranges, is "no". But given that, I like your work-around.

As I noted elsewhere, I have seen this same limitation on other excel functions, and I wish they would document it, and thus be more precise (=correct) in their documentation.

Originally Posted by guthrie

it seems like the answer to my original question - can countif (and similar functions) work over named disjoint ranges, is "no".

That is correct.

@rorya: I like your examle, but the evaluation surprises me - #value in sums gets evaluated to a good number!?countif2.jpg

@AliGW: Yes, I already answered it in the original post - I was just asking if that was correct(!).

The definition /documentation/ for these says that they work over a range, and gives no restrictions on that. While one could say it is not precise, I think more precisely it is wrong, they do not work for any range, only certain specific limited types of ranges. Yes, no examples show this, since ... it doesn't work!

Originally Posted by guthrie

@rorya: I like your examle, but the evaluation surprises me - #value in sums gets evaluated to a good number!?Attachment 581604

The formula evaluation tool is limited, especially where array coercion is involved.