Listing Question

[superbeast326]

I have a list of eight names.

Is there a formula where I can make the first name repeat from a1:a6, second a7:a12, so on and so forth.

 

[superbeast326]

Okay, if that is the case. I just got another quick question. Before I say what it is, I want to extend my gratitude to you, from the botton of my heart, for your kind and willing replies. So here is my question, If I have a column (E2:E51), that is going to filled with names and I want to find the sum of the instances where each of my eight names appear in the column, I would I go about doing it?

 

[Jon von der Heyden]

Try

=SUMPRODUCT(COUNTIF(E2:E51,Names))

Where Names refers to your 8 names range.

 

[superbeast326]

Should I enter the formula above as and array formula or a normal one. By the way, thanks again.

 

[superbeast326]

By the way, how does the formula work?

 

[superbeast326]

I have already tried it, It works like a charm. All I need now is an understanding.

 

[Jon von der Heyden]

You don't need to array enter it because we are using SUMPRODUCT. Had we been using SUM then, yes, it would have needed committing with CSE.

How it works:

COUNTIF(Range,Criteria)

Countif is typically used to count a single criterion. We can however feed an array of values to the criteria argument, for example:

COUNTIF(Range,{"x";"y";"z"})

And in turn this will yield an array of results. For example {2;1;2} (e.g. suggesting two occurrences of "x", 1 of "y" and 2 of "z"). Typical of all array formula methods, Excel doesn't know what to do with the array result until we instruct it what to do with the array. To pass an instruction we have to choose a worksheet function. Here you want to know the combined count so you pass SUM or SUMPRODUCT. Hence:

=SUMPRODUCT(COUNTIF(Range,Array))

 

[superbeast326]

So that means, adding up numbers in an array, without CSE, requires sumproduct()

 

[Jon von der Heyden]

Yes. SUMPRODUCT processes arrays and therefore does not require explicit instruction to treat it as an array formula.

 

[superbeast326]

I have now come across another conundrum, I have named formulas. Is it possible to using those formulas in a Data Validation List?

 

[Jon von der Heyden]

Yes, provided those named formulae yield a valid range. Invoke DV, and in the list range enter the name. You may have to qualify the sheet as well if the name is scoped to a worksheet.